CODEWITHSUNDEEP
vbaexcelms-word
wabi-sabi
wabi-sabi

Reputation: 21

How to export all the tables in a word document to separate excel worksheets

New to VBA, Below is my word vba code to try and read in all the tables in a word document to separate excel worksheets (one for each table). I get an error at line:

WS.Cells(i, j) = myTable.Cell(i, j)

saying that:

the requested member of the collection does not exist

After some troubleshooting the issue seems to be with myTable.Cell(i,j), but sizing of each table should deal with that...? Thoughts, advice? Thanks!

Sub ReadTablesToExcel()
Dim myTable As Table
Dim RowsCount As Integer
Dim ColumnsCount As Integer
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
Dim oExcel1 As Object
Set oExcel1 = oExcel.Workbooks.Open("C:\Users\Mike\Desktop\Book3.xlsx")
    For Each myTable In ActiveDocument.Tables
    Dim WS As Object
    Set WS = oExcel1.Activesheet
        RowsCount = myTable.Rows.Count
        ColumnsCount = myTable.Columns.Count
        For i = 1 To RowsCount
            For j = 1 To ColumnsCount
                WS.Cells(i, j) = myTable.Cell(i, j)
            Next j
        Next i
    Next myTable
ActiveDocument.Repaginate
End Sub

Upvotes: 2

Views: 775

Answers (1)

kitap mitap
kitap mitap

Reputation: 709

Your code could have been run without throwing error. ı added just code which is adding new sheets for each table.

This works: (Windows XP, Office 2007)

Sub ReadTablesToExcel()
    Dim myTable As Table
    Dim RowsCount As Integer
    Dim ColumnsCount As Integer
    Dim oExcel As Object
    Set oExcel = CreateObject("Excel.Application")
    Dim oExcel1 As Object
    Set oExcel1 = oExcel.Workbooks.Open("C:\Users\Mike\Desktop\Book3.xlsx")

    For Each myTable In ActiveDocument.Tables

        Dim WS As Object
        oExcel1.Sheets.Add
        Set WS = oExcel1.ActiveSheet

        RowsCount = myTable.Rows.Count
        ColumnsCount = myTable.Columns.Count

        For i = 1 To RowsCount
            For j = 1 To ColumnsCount
                WS.Cells(i, j) = myTable.Cell(i, j)
            Next j
        Next i

    Next myTable

    oExcel1.Close (True) 'Closes the workbook by saving changes.
    Set oExcel1 = Nothing
    ActiveDocument.Repaginate

End Sub

Upvotes: 2

Related Questions