Data update script run when opening Word document

[throwawayhelp]

Okay so I read on MakeUseOf - How to Integrate Excel Data Into a Word Document and I need some help. In the last section it talks about being able to update all the labels without needing a button in the Word document. I was wondering how to do that.

 

[Macropod]

Presumably, you're referring to: https://www.makeuseof.com/tag/integrate-excel-data-word-document/. The sample code there uses an ActiveX button in Word to update the document via a macro named:
Private Sub CommandButton1_Click()
To do away with the ActiveX button and make the process automatic, simply rename the macro:
Private Sub Document_Open()
and makes sure it's stored in the 'ThisDocument' code module of the document or its template.

 

[throwawayhelp]

Yes! Okay so far I created a button in Excel and put this in-

Dim objWord


Dim objDoc


Set objWord = CreateObject("Word.Application")


Set objDoc = objWord.Documents.Open("C:FILE NAME")


objWord.Visible = True

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook


Set exWb = objExcel.Workbooks.Open("C:FILE NAME")


ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(5, 2)


exWb.Close


Set exWb = Nothing




Would adding in this code below to the code above make the document open and update the labels in one click(if I edit to change what you suggested)?

Private Sub CommandButton1_Click()
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("C:FILE NAME")

ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(5, 2)

exWb.Close

Set exWb = Nothing

End Sub

 

[throwawayhelp]

So I don't know if this is right or not but this is what I have-

Private Sub Document_Open()
Dim objWord


Dim objDoc


Set objWord = CreateObject("Word.Application")


Set objDoc = objWord.Documents.Open("C:Users\X\X.docm")


objWord.Visible = True

Dim objExcel As New Excel.Application

Dim exWb As Excel.Workbook


Set exWb = objExcel.Workbooks.Open("C:\X\X.xlsm")


ThisDocument.total_expenses.Caption = exWb.Sheets("Sheet1").Cells(5, 2)


exWb.Close


Set exWb = Nothing
End Sub

 

[Macropod]

The macro in the link is a Word macro, not an Excel macro. It's little wonder it doesn't work with the changes you've made...

 

[throwawayhelp]

Oh sorry. I thought that maybe combining the two would make one step perform after another. I just wanted a way to open the template(or a copy) with a button in Excel and automatically fill in the areas that I needed to from certain cells in Excel.

 

[Macropod]

That isn't at all anything like what you said in your first post or what the MakeUseOf article discusses. For that, you might use code in Excel like:

Sub Button1_Click()
Application.ScreenUpdating = False
'Note: A reference to the Word library must be set, via Tools|References
Dim wdApp As New Word.Application, WdDoc As Word.document, StrNm As String
Const BkMkNm As String = "MyBookmark"
StrNm = "C:\Users\" & Environ("UserName") & "\Documents\MyDocument.docx"
If Dir(StrNm) <> "" Then
  wdApp.Visible = False
  With wdApp
    Set WdDoc = Documents.Open(Filename:=StrNm, AddToRecentFiles:=False, Visible:=False)
    With WdDoc
      If .Bookmarks.Exists(BkMkNm) Then
        .Bookmarks(BkMkNm).Range.Text = ActiveSheet.Range("B5").Value
        .Save
        .Close True
      Else
        MsgBox "Bookmark: " & BkMkNm & " not found in:" & vbCr & StrNm
      End If
    End With
    .Quit
  End With
Else
  MsgBox "File: " & StrNm & " not found."
End If
Set WdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

to modify an existing document, or:

Sub Button1_Click()
Application.ScreenUpdating = False
'Note: A reference to the Word library must be set, via Tools|References
Dim wdApp As New Word.Application, WdDoc As Word.document, StrNm As String
Const BkMkNm As String = "MyBookmark"
StrNm = "C:\Users\" & Environ("UserName") & "\Documents\MyTemplate.dotx"
If Dir(StrNm) <> "" Then
  wdApp.Visible = False
  With wdApp
    Set WdDoc = Documents.Add(Template:=StrNm, Visible:=False)
    With WdDoc
      If .Bookmarks.Exists(BkMkNm) Then
        .Bookmarks(BkMkNm).Range.Text = ActiveSheet.Range("B5").Value
        .SaveAs2 Filename:=Split(StrNm, ".dotx")(0) & ActiveSheet.Range("B5").Value & ".docx", _
          FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        .Close True
      Else
        MsgBox "Bookmark: " & BkMkNm & " not found in:" & vbCr & StrNm
      End If
    End With
    .Quit
  End With
Else
  MsgBox "File: " & StrNm & " not found."
End If
Set WdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = True
End Sub

to create a new document from a template.

No code is required in Word.

 

[throwawayhelp]

Hi! Thank you so much! I'm sorry I wasn't able to properly explain what I was trying to do. I just had a couple more questions

1. Is it possible to replace more than one bookmark at once?
2. After trying it out, I noticed that when a bookmark was replaced, the text from the line below would move up. Is it possible so that it doesn't?

 

[Macropod]

1. Is it possible to replace more than one bookmark at once?

Certainly. For example, you might delete:
Const BkMkNm As String = "MyBookmark"
and replace:

    With WdDoc
      If .Bookmarks.Exists(BkMkNm) Then
        .Bookmarks(BkMkNm).Range.Text = ActiveSheet.Range("B5").Value
        .SaveAs2 Filename:=Split(StrNm, ".dotx")(0) & ActiveSheet.Range("B5").Value & ".docx", _
          FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        .Close True
      Else
        MsgBox "Bookmark: " & BkMkNm & " not found in:" & vbCr & StrNm
      End If
    End With
    .Quit
  End With

with:

    With WdDoc
      If .Bookmarks.Exists("Bookmark1") Then
        .Bookmarks("Bookmark1").Range.Text = ActiveSheet.Range("B5").Value
      End If
      If .Bookmarks.Exists("Bookmark2") Then
        .Bookmarks("Bookmark2").Range.Text = ActiveSheet.Range("C5").Value
      End If
      If .Bookmarks.Exists("Bookmark3") Then
        .Bookmarks("Bookmark3").Range.Text = ActiveSheet.Range("D5").Value
      End If
      .SaveAs2 Filename:=Split(StrNm, ".dotx")(0) & ActiveSheet.Range("B5").Value & ".docx", _
        FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      .Close True
    End With

2. After trying it out, I noticed that when a bookmark was replaced, the text from the line below would move up. Is it possible so that it doesn't?

The bookmark isn't replaced; if anything from below is moving up, that's because you've bookmarked a paragraph break and that's what's being deleted. The solution is to use an empty bookmark before the paragraph break. Either that or insert:
& vbCr
after each instance of:
.Value