Linked Questions

Popular Questions

With the great help of Cindy Meister code for writing into embedded MS Word template to Excel has been achieved. However the problem is that first time you open Excel and run the code everything goes smooth. Once you want to run the code again everything works until closing opened MS Word template. Excel is able to open it again and fill in data from Excel but when it is trying to close it on the second run you get an error:

"Microsoft Word has stopped working". "Windows can try to recover your information." "-> Close the program"

Then you wait some time (like 10 minutes) and try to run the code again and it goes smooth again without any error.

If you open and close Excel then everything works every time.

So it actually runs the code on the second run and saves file but cannot exit Ms Word. I have tried to set everything to =Nothing. The problem must be in some Variable that stays in memory after running the code?

Sorry I may ask question wrong but I hope you can understand from the code what it is doing.

Here is the code:

Sub opentemplateWord1()
    Dim sh As Shape
    Dim objWord As Object, objNewDoc As Object ''Word.Document
    Dim objOLE As OLEObject
    Dim wSystem As Worksheet
    Dim cell As Range
Dim xlRng As Excel.Range
Dim xlSht As Worksheet
Dim wdRng As Object 'Word.Range

    Set wSystem = Worksheets("Templates")
    ''The shape holding the object from 'Create from file'
    ''Object 2 is the name of the shape
    Set sh = wSystem.Shapes("Template1")
    ''The OLE Object contained
    Set objOLE = sh.OLEFormat.Object
    'Instead of activating in-place, open in Word
    objOLE.Verb xlOpen
    Set objWord = objOLE.Object 'The Word document

    Dim objUndo As Object 'Word.UndoRecord
   'Be able to undo all editing performed by the macro in one step
    Set objUndo = objWord.Application.UndoRecord
    objUndo.StartCustomRecord "Edit In Word"

    With objWord
        .Bookmarks.Item("Date").Range.Text = ThisWorkbook.Sheets("Main").Range("K5").Value
        .Bookmarks.Item("DocumentName").Range.Text = ThisWorkbook.Sheets("Main").Range("K6").Value
        .Bookmarks.Item("ProjectNumber").Range.Text = ThisWorkbook.Sheets("Main").Range("K6").Value

With xlSht
  Set xlRng = Sheets("Data").Range("D3", Sheets("Data").Range("D" & Rows.Count).End(xlUp))
End With

Set wdRng = .Range.Characters.Last

Set xlSht = Sheets("Data")
  For Each cell In xlRng
    wdRng.InsertAfter vbCr & cell.Offset(0, -1).Text
     Select Case LCase(cell.Value)
        Case "title"
          wdRng.Paragraphs.Last.Style = .Styles("Heading 1")
        Case "main"
          wdRng.Paragraphs.Last.Style = .Styles("Heading 2")
        Case "normal"
          wdRng.Paragraphs.Last.Style = .Styles("Data")
    End Select
  Next cell

  Set xlSht = Nothing


        objWord.SaveAs2 ActiveWorkbook.Path & "\" & _
        Sheets("Data").Range("C3").Value & ".docx"

        objUndo.EndCustomRecord
        Set objUndo = Nothing
        objWord.Undo
        .Application.Quit False
Set objOLE = Nothing

    End With
    Set objWord = Nothing
End Sub

Related Questions