How to extract multiple tables from word doc to single excel sheet

srini0712

New Member
Joined
Aug 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
How can i extract multiple tables (each table having multiple rows & columns and are different from each other) from a word document into single excel sheet. is it possible for the extracted tables to be displayed in separate worksheets with in the excel sheet.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
it's possible with XL365 for subscribers Beta channel (maybe something was changed from June 2020) and Power BI but not on XL2016 as OP showed in his profile
Thanks Sandy, i do have XL365 too.
Can we extract multiple tables at onec by using VBA
 
Upvote 0
Thanks Sandy, i do have XL365 too.
Can we extract multiple tables at onec by using VBA
1. update profile
2. if you will find vba for that you can extract single table from docx, or many tables, and load to excel into one sheet or each table into multiple sheets
3. did you try vba from post#9 ?
 
Last edited:
Upvote 0
Maybe one drive or google drive.

I am a newbie in PQ. I have watched Youtube videos. Also if i were in your position i would simply convert PDF to xls through online PDF converter
 
Upvote 0
@sandy666 @CA_Punit

I have tried the below code, where in the word doc is asked and can extact all tables. Could either of you help me how can this be modified so that we selected tables can be extracted. I found this on Word table into Excel Worksheet


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
 
Upvote 0
@sandy666 @CA_Punit

I have tried the below code, where in the word doc is asked and can extact all tables. Could either of you help me how can this be modified so that we selected tables can be extracted. I found this on Word table into Excel Worksheet


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
Were you able to compile the code? You need to go to Developer/Visual Basic/Tools/References... and add Microsoft Word xx Object Library, where xx depends on the version of the Word you have.
 
Upvote 0
This code should extract multiple tables from Word to Excel. I have tested it on a two-table Word document. The code successfully extracted the first table to sheet1 and the second to sheet2. This code, however, is very crude. It doesn't check the number of tables in Word and, then, add sheets to Excel accordingly. So, you need to know how many tables there are in Word and add sheets to Excel manually.

This code should also work if there are split or merged cells in Word tables.

VBA Code:
Sub extract_multiple_tables_from_word()

    Dim wdApp As Word.Application, wdDoc As Word.Document
    Dim oWdRange As Word.Range
    Dim wdCell As Object
    Dim table_count As Long
    Dim n As Long

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    On Error Resume Next

    Set wdApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then 'Word isn't already running

        Set wdApp = CreateObject("Word.Application")

    End If

    wdApp.Visible = True
    Set wdDoc = wdApp.ActiveDocument

    table_count = wdDoc.Tables.Count

    For n = 1 To table_count

        For Each wdCell In wdDoc.Tables(n).Range.Cells
            With wdCell
                ThisWorkbook.Sheets(n).Cells(.RowIndex, .ColumnIndex) = .Range.Text
            End With
        Next
        On Error GoTo 0

    Next n

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
 
Upvote 0
Just found a mistake I made.

VBA Code:
       Next

    Next n

        On Error GoTo 0  <- this line should be outside of the For ... Next loop.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top