Excel VBA to cycle through Word bookmarks and insert Excel range value

Mr. Plow · Dec 9th 2010

I'm trying to create a process in an Excel addin to 1) open a Word document, 2) search the document for bookmarks, then 3) insert the value from an Excel range that is named the same as the Word bookmark.

I'm hoping that I can get this to be done within Excel code and not any Word VBA. I also want this code to be pretty generic to be used with documents of varying numbers of bookmarks. So I would like to use variables and loops to the fullest extent possible.

Here's what I have so far that isn't working for me:

Code

Sub WordBookmarks()
 
Dim strDoc As String
 
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim xlRange As Excel.Range
 
strDoc = Range("c:\temp\docfile.docx").Value  
Set wdApp = New Word.Application
With wdApp
    .Visible = True
    .WindowState = wdWindowStateMaximize
End With
 
With wdApp
    .Documents.Open (strDoc)
    For Each n In ActiveDocument.Bookmarks
         .Bookmarks.Item(n.Name).Range.InsertAfter Application.Range(n.Name)
    Next
End With
 
End Sub

Display More

This is crashing on the .bookmarks.Item(n.Name) command, but I'm not certain the rest of the process works properly either.

RichardW · Dec 9th 2010

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

Maybe this will help, you can adapt to suit your needs

Code

Set wdApp = New Word.Application
Set myDoc = wdApp.Documents.Open(FName)
 
s = "Text to put after bookmark"
With myDoc.Bookmarks
.Item(Bookmark name variable).Range.InsertAfter s
End With
 
myDoc.Save
myDoc.Close
Set wdApp = Nothing
Set myDoc = Nothing

Display More

Mr. Plow · Dec 10th 2010

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

Thanks Richard. I kept my code to insert the value from cells in Excel based on a n.Name reference, but you did show me the error in my ways.

This line

Code

With wdApp

should have been

Code

With wdDoc

with the appropriate adjustments to the documents.open command.

Mr. Plow · Dec 16th 2010

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

Follow up question....

When running this more than once on the same Word document it is inserting the text multiple times at the same bookmark. Is there a way to overwrite the text that had previously been added at the bookmark?

My thought is to use Word fields, but I want to make sure the formatting from Excel gets carried over. And on the whole I absolutely hate the switches on mergefields.

Thanks.

RichardW · Dec 17th 2010

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

I haven't tried it but would this work? Let us know how you go

Code

With wdApp
    .Documents.Open (strDoc)
    For Each n In ActiveDocument.Bookmarks
         if .Bookmarks(n.Name).Range.Text=vbnullstring then
              .Bookmarks.Item(n.Name).Range.InsertAfter Application.Range(n.Name)
          else
              .Bookmarks(n.Name).Range.Text=vbnullstring
              .Bookmarks.Item(n.Name).Range.InsertAfter Application.Range(n.Name)
          end if
    Next
End With

Display More

Mr. Plow · Dec 17th 2010

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

I searched all over for other advice and found some that has worked. My complete sub works with the following:

Code

Sub PopulateWordBookmarks(strDoc As String)
'   1) opens a new Word document using the general document with path & filename in excel range "DocPathFile" as a template
'   2) for each bookmark in the template document the excel range with the same name is inserted after the bookmark
'   3) Word bookmarks with _1, _2, etc. after the name ignore the _1.  This is so a single excel range can be used multiple
'      times in the document.
 
'  MUST HAVE "MICROSOFT WORD 12.0 OBJECT LIBRARY" CHECKED IN TOOLS->REFERENCES
 
Dim intNameCount As Integer, strNameTmp As String, strName As String, tmp1 As Integer, tmp2 As Integer
Dim strValue As String
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim xlRange As Excel.Range
Set wdApp = New Word.Application
With wdApp
    .Visible = True
    .WindowState = wdWindowStateMaximize
End With
Set wdDoc = wdApp.Documents.Open(strDoc)
With wdDoc
    ReDim sbkmk(1 To .Bookmarks.Count)
 
    'populates array with bookmark names
    For tmp1 = 1 To .Bookmarks.Count
      sbkmk(tmp1) = .Bookmarks(tmp1).Name
    Next
 
    For tmp2 = 1 To .Bookmarks.Count
        strNameTmp = sbkmk(tmp2)
        For tmp1 = 1 To Len(sbkmk(tmp2))
            If Mid(strNameTmp, tmp1, 1) = "_" Then
                If IsNumeric(Mid(strNameTmp, tmp1 + 1, 1)) Then
                    tmp1 = Len(sbkmk(tmp2))
                Else
                    strName = strName & Mid(strNameTmp, tmp1, 1)
                End If
            Else
                strName = strName & Mid(strNameTmp, tmp1, 1)
            End If
        Next
        If RangeNameExists(strName) Then
            strValue = Trim(Application.Range(strName).Text)
        Else
            strValue = "MISSING"
        End If
        FillBookmark wdDoc, strValue, sbkmk(tmp2)
        strName = ""
    Next
End With
 
End Sub

Display More

using another sub.....

Code

Sub FillBookmark(ByRef wdDoc As Object, ByVal vValue As Variant, ByVal sBmName As String, Optional sFormat As String)
    Dim wdRng As Object
    
    'store the bookmarks range
   Set wdRng = wdDoc.Bookmarks(sBmName).Range
    
    'if the optional format wasn’t supplied
   If Len(sFormat) = 0 Then
        'replace the bookmark text
       wdRng.Text = vValue
    Else
        'replace the bookmark text with formatted text
       wdRng.Text = Format(vValue, sFormat)
    End If
    
    're-add the bookmark because earlier process destroyed it
   wdRng.Bookmarks.Add sBmName, wdRng
    
End Sub

Display More

I lost the link to the site that created this FillBookmark process, but I'm infinitely grateful. I wish I could give you the due recognition for your help.

RichardW · Dec 17th 2010

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

Thanks for posting the final code, you have helped every member who might be looking for a similar solution, if only there were a few more who would conclude their threads to finish the story for the benefit of all.

Richard

ZappAstrim · Aug 15th 2011

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

Hello, I know that this thread has been inactive for a long time, but this is EXACTLY what I am looking for and I unfortunately cannot get it to work. I have copied & pasted the code from the 6th post into a new VBA module in my Microsoft Excel 2007 Spreadhseet and I have ensured that I have the Microsoft Word 12 Object Library selected in the references section.

My first issue is that when I try to run the sub PopulateWordBookmarks I can't. My Excel doesn't like the (strDoc As String) in brackets. When I remove that and just write "Dim strDoc As String" I can run the script, but I get stuck on this line:

If RangeNameExists(strName) Then

with the error "Compile Error: Sub or Function not defined". I am a bit of a noob to VBA, so any help that you can offer would really be appreciated. I am probably doing something really basic and really simple wrong, but I just can't figure it out. Thanks!

Mr. Plow · Aug 15th 2011

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

defining the variable in the first line of the sub should work. not sure why excel doesn't like that, but if you have a work-around then maybe you're ok.

try this for the RangeNameExists sub:

Code

Public Function RangeNameExists(bname) As Boolean
'Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Names(bname)
If Err.Number = 0 Then
RangeNameExists = True
Else
RangeNameExists = False
End If
End Function

Display More

Hope that works for you

ZappAstrim · Aug 15th 2011

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

That's absolutely brilliant! Thanks so much for that.

Is there a way to be able to modify this particular bit of VBA to be able to paste graphs and tables from the Excel document. Currently when I try to "autocopy" from a range that is a table I get the error: "Run-time error '94': "Invalid use of Null" on the following line:

strValue = Trim(Application.Range(strName).Text)

I guess I would need to build an if statement for the vba script to be able to determine between text and between tables/graphs. I am a bit lost at the moment as to how I could do this. Any help here would also be massively appreciated.

cytop · Aug 15th 2011

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

Please refer to the rules ZappAstrim, in particular #8 - http://www.ozgrid.com/forum/announcement.php?f=8

Posting in another members thread, unless suggesting a possible solution or requesting clarification of that members' issue, is not allowed.

While you are welcome to start your own thread, your post in this thread has been deleted.

cytop · Aug 24th 2011

Re: Excel VBA to cycle through Word bookmarks and insert Excel range value

Thread continued at http://www.ozgrid.com/forum/showthread.php?t=157163...

Excel VBA to cycle through Word bookmarks and insert Excel range value

Participate now!

Share