Hi there! Thank you for taking the time to read my question.
I am working on a report generator in VBA that runs from a macro-enabled word document, and queries a macro-enabled excel workbook.
I have managed to get values within specific cells to update bookmarks within the word document.
However, I get errors that certain bookmarks cannot be found, but only the ones that are within text boxes.
Here is my code:
Public Sub RepGenWord() Dim oDoc As ThisDocument Dim xlApp As Excel.Application Dim xlSh As Excel.Workbook Dim FileToOpen As String Dim XAddress As String, XClient As String, XPhone As String, XEmail As String Set xlApp = CreateObject("Excel.Application") 'xlApp.Visible = True FileToOpen = Excel.Application.GetOpenFilename("AD Sample Floor Excel Files (*.xlsm), *.xlsm", , "Select AD Sample Floor Excel File", , False) xlApp.Visible = True Set xlSh = xlApp.Workbooks.Open(FileToOpen) xlSh.Activate XClient = xlSh.Sheets("Job Details").Cells(3, 2) XAddress = xlSh.Sheets("Job Details").Cells(4, 2) XPhone = xlSh.Sheets("Job Details").Cells(7, 2) XEmail = xlSh.Sheets("Job Details").Cells(8, 2) SetBookmarkText ThisDocument, "Client1", XClient SetBookmarkText ThisDocument, "Client2", XClient SetBookmarkText ThisDocument, "Address1", XAddress SetBookmarkText ThisDocument, "Address2", XAddress SetBookmarkText ThisDocument, "Phone", XPhone SetBookmarkText ThisDocument, "Email", XEmail End Sub
This is the error I am getting:
The error repeats for 'Address1' but all the other ones update as expected.
Thank you and have a good day!