?
Solved

Excel VBA Loop through cells and assign data to Word Bookmarks

Posted on 2014-04-02
6
Medium Priority
?
1,125 Views
Last Modified: 2014-04-03
Hi

What Excel VBA would I use to loop through cells and assign data to Word Bookmarks
0
Comment
Question by:Murray Brown
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:DrTribos
ID: 39971794
Are the cells in a Word table or in excel?
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39971803
Also will the target document be open already and does it have a specific name?
0
 

Author Comment

by:Murray Brown
ID: 39971883
Hi. The cells are in Excel and the target doc is closed
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
DrTribos earned 2000 total points
ID: 39973909
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.

HTH

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
    Err.Clear
    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

0
 

Author Closing Comment

by:Murray Brown
ID: 39974224
Great! Thanks very much
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39974591
No worries, glad to help :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question