from within word, copy and paste as picture a named excel range

Question

I'm stuck coding a command button for word. The button (in a word document) needs to call a dialog to specify the file name for an excel workbook, then copy a named range and paste it back into word as a picture. the copy and paste part is fairly straightforward, but getting the file name dialog is not working for me.

Pretty much every example I found specifies the excel file name within the code, not from a dialog

My code so far (tried to clean it up as far as possible, there was a lot of trial and error)

Sub CRA_copy()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim dlgOpen As FileDialog
Dim crabook As String

oName = ActiveDocument.Name

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
   ExcelWasNotRunning = True
   Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'Open the workbook     
 crabook = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=False)

'Process each of the spreadsheets in the workbook
oXL.ActiveWorkbook.Range("CRA").Copy

If ExcelWasNotRunning Then
    oXL.Quit
End If

oName.Activate

Selection.EndKey Unit:=wdStory
Document.InsertBreak Type:=wdPageBreak

Selection.Paste
'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
    MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
    "Error: " & Err.Number
If ExcelWasNotRunning Then
    oXL.Quit
End If

End Sub

Have you just changed your entire post code ? I rolled it to your previous post, if this question in answered, then mark as answer. Afterwards, open a new post, with your new requests and add your modified code, otherwise post will never get closed here — Shai Rado
I suggest you take the tour, on how to ask questions and how to get answers on SO , right here stackoverflow.com/tour — Shai Rado
Does this really post an image? Looks like it might paste a link or formatted text but not an image. — S Meaden
Thanks @Shai Rado, worked! @S Meaden, not yet, but the current version does. — ahumble_bumblebee

Shai Rado Shai Rado · Accepted Answer · 2017-02-06T06:57:01

In Word VBA the equivalent to Excel's Application.GetOpenFilename is Application.FileDialog.

Try the code below:

Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

' modify the FileDialog settings
With dlgOpen
     'Add a filter that includes .xl* (.xls, .xlsx, .xlsm)
    .Filters.Add "Excel Files (*.xl*)", "*.xl*"
    .AllowMultiSelect = False
    .Show

    crabook = .SelectedItems(1)
End With

from within word, copy and paste as picture a named excel range

1 Answers