Search code examples
excelvbams-wordbookmarks

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!

Solution

  • 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