Choose a table to copy from a Word file into an Excel file

Question

I have a word file with 7 tables and I want to copy one of them into an excel file. But I’d like to choose each time I run the macro which table I want to copy as I’ll have different word files with different number of tables and a different table to copy.

By now I have this code that copies the word table you have selected at that moment:

    Dim wrdTbl As Table
    Dim RowCount As Long, ColCount As Long, i As Long, j As Long
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object

    Set wrdTbl = Selection.Tables(1)
    ColCount = wrdTbl.Columns.Count
    RowCount = wrdTbl.Rows.Count

    Set oXLApp = CreateObject("Excel.Application")

    oXLApp.Visible = False

    Set oXLwb = oXLApp.Workbooks.Open("C:\Sample.xlsx")
    Set oXLws = oXLwb.Sheets(1)

    For i = 1 To RowCount
        For j = 1 To ColCount
            Debug.Print wrdTbl.Cell(i, j).Range.Text

           With oXLws
                .Cells(1, 1).Value = wrdTbl.Cell(i, j).Range.Text
            End With
        Next
    Next
    oXLwb.Close savechanges:=True
    Set oXLws = Nothing
    Set oXLwb = Nothing
    oXLApp.Quit
    Set oXLApp = Nothing

    MsgBox "DONE"
End Sub

PS: Another thing I don’t understand is why I get a strange mark in each cell of the table when I get to copy it to Excel. I get like that “[]” which I do not understand why it appears because it is not in the word table. Sorry for the bad quality of the photo, I had to do it with my phone. enter image description here

macropod macropod · Accepted Answer · 2020-06-03T01:02:42

You could, for example, replace:

Set wrdTbl = Selection.Tables(1)

with:

Set wrdTbl = ActiveDocument.Tables(InputBox("Table # to copy? There are " & ActiveDocument.Tables.Count & " tables to choose from."))

To eliminate the end-of-cell marker from Word you could use:

.Cells(1, 1).Value = Split(wrdTbl.Cell(i, j).Range.Text, vbCr)(0)

Choose a table to copy from a Word file into an Excel file

1 Answers