How to export all Word tables to Excel at once
Guys, in this video I've provided code to automatically export all tables from a Microsoft Word file to Excel using Visual Basic.
Simply copy and paste the following code into This Document, within Visual Basic, in the Developer Tools:
JUST COPY THE CODE BELOW, PASTE AND RUN:
Sub ExportAllWordTablesToExcel()
' 1. Dim WordApp as Object
Dim WordDoc as Object
Dim ExcelApp as Object
Dim ExcelBook as Object
Dim ExcelSheet as Object
Dim table As Object
Dim i As Long, j As Long
Dim totalRows As Long
Dim totalColumns As Long
Dim cellText as String
Dim tblCount As Long
' 2. On Error Resume Next
Set WordApp = GetObject(Class:="Word.Application")
On Error GoTo 0
' 3. Set WordDoc = WordApp.ActiveDocument
' 4. If WordDoc.Tables.Count = 0 Then
MsgBox "The active document contains no tables.", vbExclamation
Exit Sub
End If
' 5
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True ' Make Excel visible
' 6
Set ExcelBook = ExcelApp.Workbooks.Add
' 7
For tblCount = 1 For WordDoc.Tables.Count
' Set the current table
Set table = WordDoc.Tables(tblCount)
' 8
totalLines = table.Rows.Count
totalColumns = table.Columns.Count
' 9
If tblCount = ExcelBook.Sheets.Count Then
ExcelBook.Sheets.Add After:=ExcelBook.Sheets(ExcelBook.Sheets.Count)
End If
Set ExcelSheet = ExcelBook.Sheets(tblCount)
ExcelSheet.Name = "Table " & tblCount
' 10
For i = 1 For totalLines
For j = 1 For totalColumns
' 11
On Error Resume Next
cellText = table.Cell(i, j).Range.Text
On Error GoTo 0
' 12
cellText = Replace(cellText, Chr(13), "") ' Removes the Enter (paragraph) character
cellText = Replace(cellText, Chr(7), "") ' Removes other unwanted characters
' Places the cell text in the Excel spreadsheet
ExcelSheet.Cells(i, j).Value = cellText
Next j
Next i
Next tblCount
' 13
Set WordDoc = Nothing
Set WordApp = Nothing
Set ExcelApp = Nothing
End Sub