[Solved] VBA: Range of data from Excel into Word VBA

  • Hi there, please help.

    I am using Word VBA to import a range of data (may include chart) from Excel. In Word, I used Inlineshape object to act as a Excel object. Once the data is imported, user needs to be able to modify if they wish; however, the file is not linked to original file. I have problem importing the data into excel. The pastespecial method somehow does not work. Here are the code:

    Sub Test7()

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    On Error GoTo Err_Test
    Application.ScreenUpdating = False
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(FileName)
    xlBook.Application.Visible = False
    xlBook.Application.WindowState = xlMinimized

    Dim InShape As InlineShape
    Dim objXL As Object
    Dim gXL As Excel.Worksheet

    ActiveDocument.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.8", LinkToFile _
    :=False, DisplayAsIcon:=False
    Set InShape = ActiveDocument.InlineShapes(1)
    Set objXL = InShape.OLEFormat.Object
    Set gXL = objXL.ActiveSheet

    ' Method 1 succeed only when in debug mode
    xlBook.Application.Range("A30:P88").Copy Destination:=gXL.Range("A1")

    ' Method 2 Fails completely
    gXL.PasteSpecial xlPasteValues
    xlApp.CutCopyMode = False
    SendKeys "{esc}", True

    xlBook.Close False
    xlApp.CutCopyMode = False
    Set xlBook = Nothing
    Set xlApp = Nothing
    Set InShape = Nothing
    Set xlObj = Nothing
    Application.ScreenUpdating = True
    Exit Sub
    MsgBox Err.Description
    GoTo Exit_Test

    End Sub

    I have method 1 and method 2, but none of them seems to work.

    Thank you for your help.

  • Hi and welcome to the board :)

    Below You find a solution that I regular use from XL to Word.

    Hopefully You may find some part of it workable in Your case.

    Kind regards,

  • As Dennis has done, I post below portions of some code I've been using to copy data from Excel and paste it (formatted) into Word. Perhaps this will provide some additional ideas for you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!