Macro to export MS Word tables to Excel sheets

Question

I have a word document with many tables. Does anyone know how to write a macro to export such tables to different Excel sheets?

Answer 1

Answer taken from: http://www.mrexcel.com/forum/showthread.php?t=36875

Here is some code that reads a table from Word into the active worksheet of Excel. It prompts you for the word document as well as the table number if Word contains more than one table.

Sub ImportWordTable() Dim wdDoc As Object Dim wdFileName As Variant Dim TableNo As Integer 'table number in Word Dim iRow As Long 'row index in Excel Dim iCol As Integer 'column index in Excel  wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _ "Browse for file containing table to be imported")  If wdFileName = False Then Exit Sub '(user cancelled import file browser)  Set wdDoc = GetObject(wdFileName) 'open Word file  With wdDoc     TableNo = wdDoc.tables.Count     If TableNo = 0 Then         MsgBox "This document contains no tables", _         vbExclamation, "Import Word Table"     ElseIf TableNo > 1 Then         TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _         "Enter table number of table to import", "Import Word Table", "1")     End If     With .tables(TableNo)         'copy cell contents from Word table cells to Excel cells         For iRow = 1 To .Rows.Count             For iCol = 1 To .Columns.Count                 Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)             Next iCol         Next iRow     End With End With  Set wdDoc = Nothing  End Sub 

This macro should be inserted into Excel (not Word) and put into a standard macro module rather than into the worksheet or workbook event code modules. To do this, go to the VBA (keyboard Alt-TMV), insert a macro module (Alt-IM), and paste the code into the code pane. Run the macro from the Excel interface as you would any other (Alt-TMM).

If your document contains many tables, as would be the case if your 100+ page table is actually a separate table on each page, this code could easily be modified to read all the tables. But for now I am hoping it is all one continuous table and will not require any modification.

---

Keep Excelling.

Damon

VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Answer 2

I changed this one with an addition to loop through all tables (starting from the chosen table):

Option Explicit  Sub ImportWordTable()  Dim wdDoc As Object Dim wdFileName As Variant Dim tableNo As Integer 'table number in Word Dim iRow As Long 'row index in Excel Dim iCol As Integer 'column index in Excel Dim resultRow As Long Dim tableStart As Integer Dim tableTot As Integer  On Error Resume Next  ActiveSheet.Range("A:AZ").ClearContents  wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _ "Browse for file containing table to be imported")  If wdFileName = False Then Exit Sub '(user cancelled import file browser)  Set wdDoc = GetObject(wdFileName) 'open Word file  With wdDoc     tableNo = wdDoc.tables.Count     tableTot = wdDoc.tables.Count     If tableNo = 0 Then         MsgBox "This document contains no tables", _         vbExclamation, "Import Word Table"     ElseIf tableNo > 1 Then         tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _         "Enter the table to start from", "Import Word Table", "1")     End If      resultRow = 4      For tableStart = 1 To tableTot         With .tables(tableStart)             'copy cell contents from Word table cells to Excel cells             For iRow = 1 To .Rows.Count                 For iCol = 1 To .Columns.Count                     Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)                 Next iCol                 resultRow = resultRow + 1             Next iRow         End With         resultRow = resultRow + 1     Next tableStart End With  End Sub 

Next trick: working out how to extract a table within a table from Word... and do I really want to?

TC