Import tables from Word into Excel

How to import tables from a Word document into Excel using VBA

METHOD 1. Import tables from Word into Excel using VBA

VBA

Sub Import_Tables_from_Word()

'declare variables
Dim ws As Worksheet
Dim WordFilename As Variant
Dim Filter As String
Dim WordDoc As Object
Dim tbNo As Long
Dim RowOutputNo As Long
Dim RowNo As Long
Dim ColNo As Integer
Dim tbBegin As Integer

Set ws = Worksheets("Analysis")

Filter = "Word File New (*.docx), *.docx," & _
"Word File Old (*.doc), *.doc,"

'clear all of the content in the worksheet where the tables from the Word document are to be imported
ws.Cells.ClearContents
'if you only want to clear a specific range, replace .Cells with the range that you want to clear

'displays a Browser that allows you to select the Word document that contains the table(s) to be imported into Excel
WordFilename = Application.GetOpenFilename(Filter, , "Select Word file")

If WordFilename = False Then Exit Sub

'open the selected Word document
Set WordDoc = GetObject(WordFilename)

With WordDoc

tbNo = WordDoc.tables.Count

If tbNo = 0 Then
MsgBox "This document contains no tables"
End If

'nominate which row to begin inserting the data from. In this example we are inserting the data from row 1
RowOutputNo = 1

'go through each of the tables in the Word document and insert the data from each of the cells into Excel
For tbBegin = 1 To tbNo

With .tables(tbBegin)

For RowNo = 1 To .Rows.Count

For ColNo = 1 To .Columns.Count
ws.Cells(RowOutputNo, ColNo) = Application.WorksheetFunction.Clean(.cell(RowNo, ColNo).Range.Text)
Next ColNo
RowOutputNo = RowOutputNo + 1

Next RowNo

End With
RowOutputNo = RowOutputNo

Next tbBegin

End With

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.

PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.

ADJUSTABLE PARAMETERS
Worksheet Name: Select the worksheet where you want to import the data by changing the Analysis worksheet name in the VBA code to any worksheet in the workbook.
Message Box: Change the message that will be displayed if there are no tables in the Word document by replacing the current text in the double quotation marks "This document contains no tables".

Explanation about how to import tables from a Word document into Excel

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to import tables from a Word document into Excel using VBA.

The VBA code in this tutorial will open a browser window which will allow you to select the Word document which contains the tables that you want to import into Excel. Once the Word document is selected and the 'Open' button clicked, the VBA code will import into Excel all of the data that is captured in the tables.