Excel VBA Loop through cells and assign data to Word Bookmarks


What Excel VBA would I use to loop through cells and assign data to Word Bookmarks
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
DrTribosConnect With a Mentor Commented:
OK, so this assumes you have a named range BMD (BookMarkData) in your workbook and that range is a single column.

The BMD range is saved to an array (hence the dimension are important) and the array is used to creat a table in MS word.  Number of rows = number of items in the array.

The array then loops and adds a bookmark to each cell in the table.

The code can be modified to find the bookmark and replace text, for that you might have a 2nd column in your spreadsheet?

The rest of the code is used to open MS Word... you might need to tweak a bit if you want to specifiy a file name.


Sub BMD()
Dim arrBMD()
Dim i As Integer
Dim WrdApp As Object
Dim WrdDoc As Object
Dim bstartapp As Boolean

arrBMD = Range("BMD")

On Error Resume Next
Set WrdApp = GetObject(, "Word.Application")

If Err Then
    bstartapp = True
    Set WrdApp = CreateObject("Word.Application")
End If
WrdApp.Visible = True
On Error GoTo 0

Set WrdDoc = WrdApp.documents.Add

Dim rng As Word.Range
Set rng = WrdDoc.StoryRanges(wdMainTextStory)

WrdDoc.tables.Add rng, UBound(arrBMD, 1), 1

For i = 1 To UBound(arrBMD, 1)
 WrdDoc.tables(1).Rows(i).Range.bookmarks.Add (arrBMD(i, 1))
Next i

End Sub

Open in new window

Are the cells in a Word table or in excel?
Also will the target document be open already and does it have a specific name?
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. The cells are in Excel and the target doc is closed
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Great! Thanks very much
No worries, glad to help :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.