CODEWITHSUNDEEP
excelvbams-word
Benjamin Brannon
Benjamin Brannon

Reputation: 315

Create word document from template from Excel when word isn't open

I have an excel file that generates some graphics, and I am trying to create a report in Word that pulls some of these graphics. I have everything set up and working, but the Word file isn't generated unless Word is already open. This is a snippet of what I have so far - what am I missing?

Sub Generate_Report()
Dim wordApp As Object
Dim templateFile As Object
On Error Resume Next

' Define template word file
Set wordApp = GetObject(, "Word.Application") 'gives error 429 if Word is not open
If Err = 429 Then
    Set wordApp = CreateObject("Word.Application") 'creates a Word application
    ' wordapp.Documents.Open ThisWorkbook.Path & "\WeatherShift_Report_Template.docm"
    wordApp.Visible = True
    Err.Clear
End If

Set templateFile = wordApp.Documents.Add(template:=ThisWorkbook.Path & "\WeatherShift_Report_Template.docm")

' Copy charts to new word file
Sheets("Dashboard").Select
ActiveSheet.ChartObjects("Chart 18").Activate
ActiveChart.ChartArea.Copy
With templateFile.Bookmarks
    .Item("dbT_dist_line").Range.Paste
End With

Upvotes: 2

Views: 1684

Answers (1)

Tim Williams
Tim Williams

Reputation: 166735

Your On Error Resume Next may be masking a later error.

Try this:

Sub Generate_Report()

    Dim wordApp As Object
    Dim templateFile As Object

    On Error Resume Next
    Set wordApp = GetObject(, "Word.Application") 'gives error 429 if Word is not open
    On Error Goto 0 'stop ignoring errors
    If wordApp Is Nothing Then
        Set wordApp = CreateObject("Word.Application") 'creates a Word application
    End If

    wordApp.Visible = True '<< edit

    Set templateFile = wordApp.Documents.Add(template:=ThisWorkbook.Path _
                                      & "\WeatherShift_Report_Template.docm")

    ' Copy charts to new word file
    Sheets("Dashboard").Select
    ActiveSheet.ChartObjects("Chart 18").Activate
    ActiveChart.ChartArea.Copy
    With templateFile.Bookmarks
        .Item("dbT_dist_line").Range.Paste
    End With
End Sub

Upvotes: 4

Related Questions