Hi,
I'm hoping that someone can suggest a solution to the following problem:
I have a series of letter templates in Word (around 70 or so), that largely stay the same, but have certain fields that need to be regularly updated. Many of the fields that need to be updated have multiple instances throughout one or more of the templates.
I set up an Excel spreadsheet with a list of all of the fields that ever need to be updated in the templates. As a test, I went through just one of the templates and set the initial instance of 3 fields to be updated as Bookmarks. I then replaced all subsequent instances of each field to be updated as Cross-references to the relevant Bookmark. I attempted to write a macro in the Excel workbook to open the relevant Word template, set each of the Bookmarks to the corresponding value from the Excel worksheet, and then "Update Field" for all Cross-references in the document, so that all updates to the Bookmarked fields would flow through to the other instances of those fields in the document.
Here is my macro so far:
Sub importdatafromexcel()
Dim temp_file As String
Dim save_doc_as As String
Dim excel_input As Worksheet
Dim wordApp As Object
Set wordApp = CreateObject("word.application")
Set excel_input = Worksheets("Sheet1")
temp_file = "filepath"
save_doc_as = "filepath"
With wordApp
.Visible = True
.Documents.Add Template:=(temp_file)
Set wordDoc = .activedocument
wordDoc.bookmarks("Bookmark1").Select
.Selection.InsertAfter (excel_input.Cells(3, 16))
wordDoc.bookmarks("Bookmark2").Select
.Selection.InsertAfter (excel_input.Cells(3, 17))
wordDoc.bookmarks("Bookmark3").Select
.Selection.InsertAfter (excel_input.Cells(3, 18))
End With
wordApp.activedocument.SaveAs Filename:=save_doc_as
End Sub
The macro works all the way through, and even highlights the relevant bookmarks, but doesn't replace the bookmark text with the input text from Excel. Does anyone have any idea how to make this work? Also does anyone know how to update all the cross-references once the bookmark has been updated?
Any help would be greatly appreciated!
Thanks
I'm hoping that someone can suggest a solution to the following problem:
I have a series of letter templates in Word (around 70 or so), that largely stay the same, but have certain fields that need to be regularly updated. Many of the fields that need to be updated have multiple instances throughout one or more of the templates.
I set up an Excel spreadsheet with a list of all of the fields that ever need to be updated in the templates. As a test, I went through just one of the templates and set the initial instance of 3 fields to be updated as Bookmarks. I then replaced all subsequent instances of each field to be updated as Cross-references to the relevant Bookmark. I attempted to write a macro in the Excel workbook to open the relevant Word template, set each of the Bookmarks to the corresponding value from the Excel worksheet, and then "Update Field" for all Cross-references in the document, so that all updates to the Bookmarked fields would flow through to the other instances of those fields in the document.
Here is my macro so far:
Sub importdatafromexcel()
Dim temp_file As String
Dim save_doc_as As String
Dim excel_input As Worksheet
Dim wordApp As Object
Set wordApp = CreateObject("word.application")
Set excel_input = Worksheets("Sheet1")
temp_file = "filepath"
save_doc_as = "filepath"
With wordApp
.Visible = True
.Documents.Add Template:=(temp_file)
Set wordDoc = .activedocument
wordDoc.bookmarks("Bookmark1").Select
.Selection.InsertAfter (excel_input.Cells(3, 16))
wordDoc.bookmarks("Bookmark2").Select
.Selection.InsertAfter (excel_input.Cells(3, 17))
wordDoc.bookmarks("Bookmark3").Select
.Selection.InsertAfter (excel_input.Cells(3, 18))
End With
wordApp.activedocument.SaveAs Filename:=save_doc_as
End Sub
The macro works all the way through, and even highlights the relevant bookmarks, but doesn't replace the bookmark text with the input text from Excel. Does anyone have any idea how to make this work? Also does anyone know how to update all the cross-references once the bookmark has been updated?
Any help would be greatly appreciated!
Thanks
