I can't get this Macro to run when opening a document

0
votes
Private Sub Document_Open()
    Dim objExcel As Object
    Dim FileName As String
    Dim cc As ContentControl

    FileName = "Z:\" & "\Data.xlsx"
    Set objExcel = CreateObject("Excel.Application")
    Set exWb = objExcel.Workbooks.Open(FileName)

    For Each cc In ActiveDocument.SelectContentControlsByTag("Name")
        cc.Range.Text = exWb.Sheets("4").Cells(1, 2)
    Next

    exWb.Close
    Set exWb = Nothing
End Sub

The file is saved as a Word Macro-Enabled Template so double clicking it creates a new file called Document1 but the Macro doesn't appear to run because the content control is blank. However if I run the same code via "Macros" (under Developer), the form fills in.

1
vbamacrosms-word

1 Answers

1
votes

I found that it's Document_New instead of Document_Open

Private Sub Document_New()
    Dim objExcel As Object
    Dim FileName As String
    Dim cc As ContentControl

    FileName = "Z:\" & "\Data.xlsx"
    Set objExcel = CreateObject("Excel.Application")
    Set exWb = objExcel.Workbooks.Open(FileName)

    For Each cc In ActiveDocument.SelectContentControlsByTag("Name")
        cc.Range.Text = exWb.Sheets("4").Cells(1, 2)
    Next

    exWb.Close
    Set exWb = Nothing
End Sub