Solved

Excel VBA Loop through cells and assign data to Word Bookmarks

Posted on 2014-04-02
6
1,060 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

773 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