Excel is my source document and Word is my template. I have put together a macro in Excel which opens my Word template and populates my bookmarks with the applicable Excel cells. My Excel document is mostly percentages which I am transferring to Word. While I was drafting my source document I was hard coding my percentages in temporarily (e.g. 30.00%, 40.00%, etc) to get it working. Well my "draft" Excel document works perfectly, but when I moved my "draft" Excel document into a "live" working document I replaced my temporary hard coded percentages into formulas which give me my percentages. Of course my macro doesn't work now.
My assumption is the code I put together in my "draft" is not populating my Word bookmarks due to the formulas I added to my Excel cells. I'm also guessing this is not a new problem and hopefully there is an easy fix that I am just not thinking of. I have pasted my code below. Any suggestions would be greatly appreciated.
Sub createTemplate()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Wal_Cat_B As Excel.Range
Dim Wal_Cat_D As Excel.Range
Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="C:\Test.doc")
Set Wal_Cat_B = Sheets("Sheet1").Range("B2")
Set Wal_Cat_D = Sheets("Sheet1").Range("B3")
With myDoc.Bookmarks
.Item("Wal_Cat_B").Range.InsertAfter Wal_Cat_B.Text
.Item("Wal_Cat_D").Range.InsertAfter Wal_Cat_D.Text
End With
errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub
My assumption is the code I put together in my "draft" is not populating my Word bookmarks due to the formulas I added to my Excel cells. I'm also guessing this is not a new problem and hopefully there is an easy fix that I am just not thinking of. I have pasted my code below. Any suggestions would be greatly appreciated.
Sub createTemplate()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Wal_Cat_B As Excel.Range
Dim Wal_Cat_D As Excel.Range
Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="C:\Test.doc")
Set Wal_Cat_B = Sheets("Sheet1").Range("B2")
Set Wal_Cat_D = Sheets("Sheet1").Range("B3")
With myDoc.Bookmarks
.Item("Wal_Cat_B").Range.InsertAfter Wal_Cat_B.Text
.Item("Wal_Cat_D").Range.InsertAfter Wal_Cat_D.Text
End With
errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub
