Copy Word table to Excel and format as table

Question

I have a macro in Word VBA that copies a table from a Word file and pastes it to Excel.

It pastes it like this:

My question now, is it possible to format that table as an “Excel table”, like when you insert a table in excel, using the vba in word?

To get the final result as this:

I mean I know how to do it using a macro in the same excel file but how can I format it from the word vba?

My problem is that I need to do it from word vba as I don’t have the option of doing it in an excel vba.

Thank you everyone!

My code is:

    Dim wrdTbl As Table, c As Long
    'Excel Objects
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object

    'Set your table
    With ActiveDocument
        If ActiveDocument.Tables.Count >= 1 Then
            Set wrdTbl = .Tables(InputBox("Table # to copy? There are " & .Tables.Count & " tables to choose from."))
        End If
    End With
    'Create a new Excel Application
    Set oXLApp = CreateObject("Excel.Application")
    With oXLApp
    'Hide Excel
        .Visible = False
        'Open the relevant Excel file
        Set oXLwb = oXLApp.Workbooks.Open("C:\Users\" & Environ("Username") & "\Desktop\ExcelEx.xlsx")
    End With
    wrdTbl.Range.Copy
    With oXLwb.Sheets(1)
        .Paste .Range("A1")
    End With
    'Close and save Excel file
    oXLwb.Close True
    'Cleanup (VERY IMPORTANT)
    oXLApp.Quit
    Set oXLwb = Nothing: Set oXLApp = Nothing
    MsgBox "Done"
End Sub

Did you try the macro recorder? Just turn macro recording on and format it as table manually once. Then check the code that was generated. — Pᴇʜ, Jun 04 '20 at 11:26
The thing is I don’t want to get to open the excel. I mean, I’ll use only word as the excel file will save in a server to be used by another program and because there are going to be so many excel files, I just want to open the word, do it in the vba there and exit. — Raúl, Jun 04 '20 at 11:31
Well, you would use the macro recoder just once to find out how the code would look like. Then you know how to format as a table with VBA. • Please also show the code you use to copy from Word to Excel. — Pᴇʜ, Jun 04 '20 at 11:38
I added my code and I do not know how to use macro recorder ... I’d prefer if it’s possible to do it without it. — Raúl, Jun 04 '20 at 11:45
I highly recommend to learn how to use the [Macro Recorder](https://www.excel-easy.com/vba/examples/macro-recorder.html) because it is a very easy way to find out how code would look like if you do something manually. You just do it and the recorder writes the code for you (that you finally can adjust). Powerful tool if you have no idea how to do something. — Pᴇʜ, Jun 04 '20 at 12:18
Thank you, I was trying to see how can I call a macro in excel using word vba. I’ll learn more too, but your answer works as I wanted except for the range. — Raúl, Jun 04 '20 at 12:22

Pᴇʜ · Accepted Answer · 2020-06-04T12:56:22.023

1

Something like that

With oXLwb.Sheets(1)
    .Paste .Range("A1")
    Dim LastRow As Long
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Dim LastCol As Long
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    .ListObjects.Add(SourceType:=xlSrcRange, Source:=.Range("A1", .Cells(LastRow, LastCol)), XlListObjectHasHeaders:=xlYes).TableStyle = "TableStyleMedium2"
End With

should format it as table. Adjust to your needs and desired style.

Here is a good resource on how to Find last row, column or last cell.

edited Jun 04 '20 at 12:56

answered Jun 04 '20 at 12:13

Pᴇʜ

56,719
10
49
73

Yes thank you! That worked perfectly, the only thing now is to adjust it to the range of the table. As it formats a bigger table than the data. How can I solve this? – Raúl Jun 04 '20 at 12:21
@Raúl That depends on your data. Is the first row and column **always** having data? Or might there be empty cells? If there is always data then see my edit. – Pᴇʜ Jun 04 '20 at 12:58
Thank you so much, exactly what I wanted. Usually in my tables all cells have data. However, I have tried it with tables with merged cells and with multi-lined cells, and it gives me an error with the first one and put two cells in different rows for the second one. Is there and option to do also merged cells and multi-lined cells tables? – Raúl Jun 04 '20 at 15:26
@Raúl Merged cells are not possible in formatted tables. Neiter in rows nor in columns. If this answered your question please mark this answer as solution. – Pᴇʜ Jun 04 '20 at 17:28
Oh, I didn't know that, thank you for everything, I'll do it now! – Raúl Jun 04 '20 at 18:01

Copy Word table to Excel and format as table

1 Answers1