Solved

Excel VBA Loop through cells and assign data to Word Bookmarks

Posted on 2014-04-02
6
1,068 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:murbro
  • 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:murbro
ID: 39971883
Hi. The cells are in Excel and the target doc is closed
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 15

Accepted Solution

by:
DrTribos earned 500 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:murbro
ID: 39974224
Great! Thanks very much
0
 
LVL 15

Expert Comment

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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

830 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