Solved

Excel VBA Loop through cells and assign data to Word Bookmarks

Posted on 2014-04-02
6
1,044 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 14

Expert Comment

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

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

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 14

Expert Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now