AddOleObject pasting Excel into Word not accepting variable string

[SeaPea]

I'm trying to paste a series of Excel workbooks into Word as imbedded objects. The list of workbooks is contained in a named range "xlFilenames" within an Excel workbook. The destinations
within Word are specified using bookmarks. The table xlFilenames contains corresponding bookmark names. My problem is that the ActiveDocument.InlineShapes.AddOLEObject method does not see my filename when it's a string variable (xlFilenames), only when I hardcode the filename (see my "path = " statement). I tried making path=xlFilename (don't ask me why), and declaring both variables as variant, both global, all to no avail etc. Driving me nuts. Any suggestions greatly appreciated. I decided to post all code and not a subroutine in case it helps.

Sub PasteImbeddedExcelFilesIntoWord()
    
    Dim path As String
    Dim Ils As InlineShape
    Dim objExcel As Object, _
        objWbk As Object, _
        objDoc As Document
    Dim sBookmark As String, _
        sWbkName As String, _
        sRange As String, _
        xlFilenames As String
    Dim BMRange As Range
    Dim bmk As Bookmark
    Dim i As Integer, _
        j As Integer, _
        k As Integer, _
        bmkCount As Integer
    Dim vNames()
    Dim vBookmarks()
    Dim dlgOpen As FileDialog
    Dim bnExcel As Boolean
    
    On Error GoTo Err_Handle
    
    'get Excel file that contains table of filenames containing a range named xlFilenames
    Set dlgOpen = Application.FileDialog( _
        FileDialogType:=msoFileDialogOpen)
    bnExcel = False
    Do Until bnExcel = True
        With dlgOpen
            .AllowMultiSelect = True
            .Show
            If .SelectedItems.Count > 0 Then
                sWbkName = .SelectedItems(1)
            Else
                MsgBox "Please select a workbook to use for processing"
            End If
        End With
        If InStr(1, sWbkName, ".xls") > 0 Then
            bnExcel = True
        Else
            MsgBox "The file must be a valid Excel file. Try again please..."
        End If
    Loop
    
    Set objDoc = ActiveDocument
    
    'check to see that the Excel file is open. If not, open the file
    Set objExcel = GetObject(, "Excel.Application")
    
    For i = 1 To objExcel.Workbooks.Count
        If objExcel.Workbooks(i).Name = sWbkName Then
            Set objWbk = objExcel.Workbooks(i)
            Exit For
        End If
    Next
    
    If objWbk Is Nothing Then
        Set objWbk = objExcel.Workbooks.Open(sWbkName)
    End If
    
    
    'switch to Excel, find range name that corresponds to the bookmark
    objExcel.Visible = True
    objWbk.Activate
    vNames = objWbk.Worksheets("Files").Range("xlFilenames").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
                sFiles = vNames(k, 2) 'sfiles will now contain the filename from Excel
                Exit For
            End If
        Next k
      
        'return to Word and paste
        objDoc.Activate
        BMRange.Select
        Selection.Delete
        
        On Error Resume Next
        ActiveDocument.Bookmarks(sBookmark).Delete
        On Error GoTo 0
 [highlight]   
        path = "C:\sourcefile.xls"  'I want to use the variable xlFilenames here instead

        Set Ils = ActiveDocument.InlineShapes.AddOLEObject( _
        FileName:=path, LinkToFile:=False, DisplayAsIcon:=False, Range:=Selection.Range) 'this doesnt work if I substitute "xlFilenames" for "path"
        [/highlight]
        Selection.Move Unit:=wdCharacter, Count:=-1
        
        objDoc.Bookmarks.Add Name:=vBookmarks(j), Range:=Selection.Range
    
    Next j
    
Err_Exit:
    Set BMRange = Nothing
    Set objWbk = Nothing
    objExcel.Visible = True
    Set objExcel = Nothing
    Set objDoc = Nothing
    
Err_Handle:
    If Err.Number = 429 Then 'excel not running; launch Excel
        Set objExcel = CreateObject("Excel.Application")
        Resume Next
    ElseIf Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Err_Exit
    End If
    
End Sub

 

[PHV]

Replace this:
FileName:=path
with this:
FileName:=sFiles

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeaPea]

PH, Thank you Thank you. At first I was wondering why you referred to "sFiles" because the variable I wanted was "xlFilenames", and I thought I had tried what you suggested. Turns out I got my variables mixed up at some point in my experimentations. I changed the declaration from xlFilenames to sFiles, changed the FileName: as you suggested and now it works like a charm. I was staring at it too long. Thanks for the help.