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

Answer 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.