Copy Table content in a word file and paste it into excel with formatting retained

Question

I wanted to copy a table in a word file to excel using excel vba. To do this, I ran the following code.

Dim write_row As Integer

Dim tableObject As Object

Dim tableItem As Variant

Dim WordApp As Object
Dim WordDoc As Word.Document
Set WordApp = CreateObject("Word.Application") ' CreateObject関数でWordをセット

WordApp.Visible = False

Set WordDoc = WordApp.Documents.Open("C:\Users\test.docx")

Set tableObject = WordDoc.Content.Tables

write_row = 1

For Each tableItem In tableObject
    tableItem.Range.Copy
    Cells(write_row, 1).PasteSpecial Paste:=xlPasteAll
    write_row = write_row + 40
Next

Application.CutCopyMode = False
WordDoc.Close

WordApp.Quit

Then excel pasted the image data of only the first part of each table out of the three tables in the word file. I did not expect that the tables in the word file would be pasted as images into excel, but the purpose was to check the contents of the tables, and the operation was lightened, so there was no problem in that respect. However, I cannot overlook the fact that the image data contains only the first part of the table. How can I copy all the table contents in the word file to excel as image data?

Excel's ability to replicate Word formatting is limited. That said, see, for example: https://www.excelguru.ca/forums/showthread.php?8900-Help-with-VBA-to-extract-data-from-Word-to-Excel&p=36586&viewfull=1#post36586 — macropod, Jun 10 '22 at 13:47

Jeremy Thompson · Answer 1 · 2022-06-09T07:13:57.347

This will copy the Word Documents' Table data as Text though it won't retain format and will remove any images:

Sub main()
Dim write_row As Integer    
Dim tableObject As Object    
Dim tableItem As Variant    
Dim WordApp As Object
Dim WordDoc As Word.Document   ' Either use LateBinding with CreateObject or EarlyBinding with Tools menu > References > Microsoft Word Object Library v
Set WordApp = CreateObject("Word.Application")    
WordApp.Visible = True    ' Check you don't get any ReadOnly prompt if the doco is open
Set WordDoc = WordApp.Documents.Open("C:\Users\JT\Downloads\Jeremy+Thompson+cv.doc")

Set tableObject = WordDoc.Content.Tables    
write_row = 1

For Each tableItem In tableObject
Dim i As Integer
Dim j As Integer
Dim value As String
    ReDim Data(1 To tableItem.Rows.Count, 1 To tableItem.Columns.Count)
    For i = 1 To tableItem.Rows.Count
            For j = 1 To tableItem.Columns.Count
                value = tableItem.Cell(i, j).Range.Text
                value = Replace(value, vbCr, vbLf)
                value = Replace(value, Chr(7), "")  ' funky char's in my Table (old word doco)
                value = Replace(value, Chr(10), "")  ' funky char's in my Table (old word doco)
                Data(i, j) = value
            Next j
        Next i

    With Range(Cells(1, 1), Cells(tableItem.Rows.Count, tableItem.Rows.Count))
        .value = Data
        .WrapText = True
    End With
    
    'tableItem.Range.Copy
    'Cells(write_row, 1).PasteSpecial Paste:=xlPasteAll
    
    write_row = write_row + 40
Next

Application.CutCopyMode = False
WordDoc.Close

WordApp.Quit
End Sub

If you want to retain Formatting that's a different question and the best I've found is via HTML https://stackoverflow.com/a/39314154/495455

Go through that answer and all you'd have to do is change all DataTable Styling Commands to the Word Table equivalents, eg: dgv.Rows[row].Cells[col].Style.ForeColor

Thank you for your answer. I don't want to change the layout of the word table, and I would like to copy the word table as an image for speed, but is it still difficult to paste the entire table as an image? Also, I feel that the method via HTML is too difficult for me. Thank you very much. — komandap, Jun 09 '22 at 08:53

Copy Table content in a word file and paste it into excel with formatting retained

1 Answers1