Microsoft Office Forums - View Single Post

gmayor · 07-14-2019, 08:36 PM

You cannot insert Excel ranges like this, only Word ranges. You can do it with a Word macro/ The following will paste the named range from the named file at the cursor:

Code:

Sub InsertExcelRange()
'Graham Mayor - https://www.gmayor.com - Last updated - 15 Jul 2019
Dim xlApp As Object
Dim xlWb As Object
Dim strWB As String
Dim bStarted As Boolean, bOpened As Boolean
Const strWorkbook As String = "workbook path"
Const xlRange As String = "excel range name"
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
        bStarted = True
    End If
    On Error GoTo 0
    'Open the workbook to input the data
    strWB = Dir(strWorkbook)
    On Error Resume Next
    Set xlWb = xlApp.Workbooks(strWB)
    If xlWb Is Nothing Then
        Set xlWb = xlApp.Workbooks.Open(strWorkbook)
        bOpened = True
    End If
    On Error GoTo 0
    xlApp.GoTo Reference:="Pension"
    xlApp.Selection.Copy
    Selection.Paste
    If bOpened Then xlWb.Close 0
    If bStarted Then xlApp.Quit
lbl_Exit:
    Set xlApp = Nothing
    Set xlWb = Nothing
    Exit Sub
End Sub

Installing Macros