Magicfan1423
03-03-2016, 09:59 AM
Hi All,
I've been struggling with a problem for several days now, and I feel I need to reach out for some help. Here's the gist:
I'm trying to utilize a Macro in Word to pull Excel named ranges into Word (note the bookmarks and named ranges have identical names). I was able to, after several hours of searching and research, find a fairly robust piece of code from a site called DataWright. However, I am having a couple of issues. The piece of the code I need help with is the component that looks at Excel and loops through the Bookmarks.
'switch to Excel, find range name that corresponds to the bookmark
objExcel.Visible = False
objWbk.Activate
vNames = objWbk.Worksheets("Lists").Range("Bookmarks").Value
'loop through the bookmarks
bmkCount = ActiveDocument.Bookmarks.Count
ReDim vBookmarks(bmkCount - 1)
j = LBound(vBookmarks)
For Each bmk In ActiveDocument.Bookmarks
vBookmarks(j) = bmk.Name
j = j + 1
Next bmk
For j = LBound(vBookmarks) To UBound(vBookmarks)
'go to the bookmark
Selection.GoTo What:=wdGoToBookmark, Name:=vBookmarks(j)
Set BMRange = ActiveDocument.Bookmarks(vBookmarks(j)).Range
For k = 1 To UBound(vNames)
If vNames(k, 1) = vBookmarks(j) Then
sSheet = vNames(k, 2)
sRange = vNames(k, 3)
Exit For
End If
Next k
'(Skipping some lines here that just copy as picture, return to Word, paste, etc.)
Next j
The two problems I'm having are:
1. I would like to not have to retain a list of Bookmarks in Excel for it to look to. This code refers to a specific sheet called "Lists" which itself has a named Range called "Bookmarks" which list the bookmark references in three columns (Bookmark, Sheet, Range Name). That is, unless the "Lists" sheet containing the Bookmarks could be hidden at all times except when the code is run, then it isn't an issue.
2. I have a defined number of bookmarks (and corresponding named ranges), but not all will be used each time so I'd like the code to be able to "skip" a Bookmark if the sheet that the corresponding Named Range is on in Excel is not Visible. (I will hide sheets the are N/A to my report is the reason, and these tables don't need to be imported to Word if they are N/A).
Thanks for taking the time to read this post. I would greatly appreciate any help you all could provide!
I've been struggling with a problem for several days now, and I feel I need to reach out for some help. Here's the gist:
I'm trying to utilize a Macro in Word to pull Excel named ranges into Word (note the bookmarks and named ranges have identical names). I was able to, after several hours of searching and research, find a fairly robust piece of code from a site called DataWright. However, I am having a couple of issues. The piece of the code I need help with is the component that looks at Excel and loops through the Bookmarks.
'switch to Excel, find range name that corresponds to the bookmark
objExcel.Visible = False
objWbk.Activate
vNames = objWbk.Worksheets("Lists").Range("Bookmarks").Value
'loop through the bookmarks
bmkCount = ActiveDocument.Bookmarks.Count
ReDim vBookmarks(bmkCount - 1)
j = LBound(vBookmarks)
For Each bmk In ActiveDocument.Bookmarks
vBookmarks(j) = bmk.Name
j = j + 1
Next bmk
For j = LBound(vBookmarks) To UBound(vBookmarks)
'go to the bookmark
Selection.GoTo What:=wdGoToBookmark, Name:=vBookmarks(j)
Set BMRange = ActiveDocument.Bookmarks(vBookmarks(j)).Range
For k = 1 To UBound(vNames)
If vNames(k, 1) = vBookmarks(j) Then
sSheet = vNames(k, 2)
sRange = vNames(k, 3)
Exit For
End If
Next k
'(Skipping some lines here that just copy as picture, return to Word, paste, etc.)
Next j
The two problems I'm having are:
1. I would like to not have to retain a list of Bookmarks in Excel for it to look to. This code refers to a specific sheet called "Lists" which itself has a named Range called "Bookmarks" which list the bookmark references in three columns (Bookmark, Sheet, Range Name). That is, unless the "Lists" sheet containing the Bookmarks could be hidden at all times except when the code is run, then it isn't an issue.
2. I have a defined number of bookmarks (and corresponding named ranges), but not all will be used each time so I'd like the code to be able to "skip" a Bookmark if the sheet that the corresponding Named Range is on in Excel is not Visible. (I will hide sheets the are N/A to my report is the reason, and these tables don't need to be imported to Word if they are N/A).
Thanks for taking the time to read this post. I would greatly appreciate any help you all could provide!