Do note there's a potential flaw in the way your code uses the bookmarks - if you output to them a second time, the new output will not replace what is already there; it will be prepended. It will also error-out if a bookmark gets deleted. If either of those is a possibility, try:
Code: Select all
Dim xl As Object
Dim wbk As Object
Dim wsh As Object
Set xl = GetObject(Class:="Excel.Application")
Set wbk = xl.ActiveWorkbook
Set wsh = wbk.Worksheets("Report Information Log")
wdDoc As Document
Set wdDoc = ActiveDocument
Call UpdateBookmark(wdDoc, "Name", wsh.Range("B5").Value)
Call UpdateBookmark(wdDoc, "Date", wsh.Range("E6").Value)
Call UpdateBookmark(wdDoc, "Date2", wsh.Range("E6").Value)
Call UpdateBookmark(wdDoc, "Address", wsh.Range("B30").Value)
Call UpdateBookmark(wdDoc, "Fees", wsh.Range("E8").Value)
With wdDoc
.Shapes(1).Visible = msoFalse
.PrintOut Background:=False
.Shapes(1).Visible = msoTrue
End With
...
End Sub
Sub UpdateBookmark(Doc As Document, StrBkMk As String, StrTxt As String)
Dim BkMkRng As Range
With Doc
If .Bookmarks.Exists(StrBkMk) Then
Set BkMkRng = .Bookmarks(StrBkMk).Range
BkMkRng.Text = StrTxt
.Bookmarks.Add StrBkMk, BkMkRng
End If
End With
Set BkMkRng = Nothing
End Sub
I'd also be inclined you use a cross-reference to your 'Date' bookmark instead of having the 'Date2' bookmark, since both are populated with the same date.