Import Excel Named Ranges into Word with VBA

[Magicfan1423]

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!

[mancubus]

welcome to the forum.

this is excel help forum and you need help in Word vba.

since they are different areas of expertise, ask a moderator to move the thread to Word help forum.

[snb]

Fortunately this subform has some more expertise than 'one-trick-Excel-ponies'.

code from Excel:

Sub M_snb()
with Getobject("","Word.application")
for each it in .activedocument.bookmarks
thisworkbook.sheets("sheet1").range(it).copy
it.range.paste
next
end with
End Sub

code from Word

Sub M_snb()
With GetObject(, "Excel.application")
For Each it In ActiveDocument.Bookmarks
.workbooks(2).sheets("sheet1").Range(it).Copy
it.Range.Paste
Next
End With
End Sub

[mikerickson]

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.The visibility of the sheet Lists won't effect the code that you posted.
It can be hidden or very hidden even when the code is running.