Copying word tableObjects to excelRange in VBA fails

[VBAneophite22]

Hi all,

What I want: copy some tables from a word document to Excel, copying them to 1 worksheet and just add some enters between each copied result.

What I tried: what works is if i use a new worksheet Object to .Paste to. But if use a Range object somehow both the .Paste and the .PasteSpecial methods fail.

I am not used to working with word table objects... but I can't figure it out. Here is an example that just requires some word document to be open with some table in it.


Code snippet

Sub ExportTablesToExcel_test()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim wdTable As Object
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim rowOffset As Long
    Dim tableIndex As Long
    Dim tableRows As Long
    Dim tableCols As Long
    Dim targetRange As Range

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Add
    Set xlWS = xlWB.Worksheets(1) ' Use the first worksheet
   

    Set wdApp = GetObject(, "Word.Application")
    Set wdDoc = wdApp.ActiveDocument

    rowOffset = 1
   
    ' Loop through all tables in the Word document
    For tableIndex = 1 To wdDoc.Tables.Count
        Set wdTable = wdDoc.Tables(tableIndex)
       
        ' Get the number of rows and columns in the Word table
        tableRows = wdTable.Rows.Count
        tableCols = wdTable.Columns.Count
        wdTable.Range.Select
       
        ' Copy the table content from Word
        wdTable.Range.Copy
       

        '< does work, but not what i want >
        xlWS.Paste
       
                '< does not work >
        ' Resize the target range in Excel to match the size of the Word table
        Set targetRange = xlWS.Cells(rowOffset, 1).Resize(tableRows, tableCols)
        targetRange.PasteSpecial xlPasteValues
   
       
        ' Adjust rowOffset to leave 2 empty rows after each table
        rowOffset = xlWS.Cells(xlWS.Rows.Count, 1).End(xlUp).Row + 3 ' 2 empty rows after the table
       
    Next tableIndex
   
    ' Cleanup
'    wdDoc.Close False ' Close the Word document without saving
'    wdApp.Quit ' Quit the Word application
    Set wdTable = Nothing
    Set wdDoc = Nothing
    Set wdApp = Nothing
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing

End Sub

 

[VBAneophite22]

Well I found the issue. I was referencing a Range Object that is either an Microsoft Word or Excel Reference Object.

So for others new to working with multiple objects like an Excel or a Word object. The fix;
- import direct variables referencing the correct library. This prevents vague "object not found" errors. So don't do dim targetRange as Object, but do: dim targetRange as Excel.Range
- if this naming .word / .excel is not working - you have to import the correct VBA reference library