CODEWITHSUNDEEP
excelvbams-wordbookmarks
MightyMouseZ
MightyMouseZ

Reputation: 79

Word VBA - insert data from excel with defined name

I have an excel worksheet with some data in it. Each cell in the worksheet has a defined name.

I also have a word document with bookmarks defined. Each bookmark has the same name as a cell in the excel.

I have my VBA code in word. Right now I am trying to 1. go to each bookmark in word 2. go to excel and go to the cell with the same defined name 3. insert the data in the cell to the location of the bookmark in word.

Here's what I have:

Dim wb As Excel.Workbook
Dim excelPath As String
Dim numBM As Integer
Dim countBM As Integer
Dim currentBM As String

numBM = ActiveDocument.Bookmarks.Count

excelPath = getFilePath() 'separate function to get the location of the excel file

Set wb = Excel.Workbooks.Open(excelPath)

For countBM = 1 To numBM
    currentBM = ActiveDocument.Bookmarks(countBM).Name

    ActiveDocument.Bookmarks(currentBM).Range.Text = wb.????????  
Next

I can get this to work if I use:

ActiveDocument.Bookmarks(currentBM).Range.Text = wb.Worksheets("Sheet1").Range(currentBM)

but I don't want to reference the worksheet. I only want to reference the cell.

I know there must be a simple solution to this!

Upvotes: 1

Views: 1294

Answers (2)

DanL
DanL

Reputation: 999

There is, indeed, a simple solution. If you've named the cells, you can access the Names collection in the Excel Application object. For example, if you want to retrieve the range (or value in your case) of a specifically named cell in Excel, you can do the following:

Excel.Names("RangeName").RefersToRange.Value

Upvotes: 1

LocEngineer
LocEngineer

Reputation: 2917

I don't want to reference the worksheet

Well, you will have to. Just do it right away and you'll only have to reference it once:

Dim ws As Excel.Worksheet
'...
Set ws = Excel.Workbooks.Open(excelPath).Sheets(1)
ActiveDocument.Bookmarks(currentBM).Range.Text = ws.Range(currentBM)

And Bob's your uncle.

Upvotes: 0

Related Questions