Word table into Excel Worksheet

[sopranoiam]

I have a 100+ page Word table that I want to put into an Excel worksheet. Is there a macro or an easy way to do this and have a coherent Excel worksheet when I finish? I end up with so many blank rows and the text is all over. I didn't create the Word doc so formatting is unruly.

Thanks, Gay

 

[Sudhirnune]

Hi Damon,

I am not sure how it is working, Can you please help me with the code for Pulling out all the tables Data to One Sheet & Each Sheet from each table.

Sudhir.

 

[storo1975]

I came across this code from the forum and absolutely love it

<code>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</code></pre>

I want to use this code to do something specific and would like some help.
I have a table that consists of 5 columns(Column1, Column2, Column3, Column4, Column5). I want the macro to go through each row in the word table, and if it finds the word "Patent" (no quotes) in Column5, export that entire row to Excel and continue with the next row until the last row in the table.

Can someone assist me with this?

 

[storo1975]

Pls disregard

I was given the solution in another forum but I am willing to share the results

<CODE>

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;*.docx", , _ "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 'determine if the text of the 5th column contains the word "Patent" If .cell(iRow, 5).Range.Text Like "*Patent*" Then 'find the last empty row in the current worksheet nextRow = ThisWorkbook.ActiveSheet.Range("a" & Rows.Count).End(xlUp).Row + 1 For iCol = 1 To .Columns.Count ThisWorkbook.ActiveSheet.Cells(nextRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text) Next iCol End If Next iRow End With End With Set wdDoc = Nothing End Sub</pre>
</CODE>

 

[asdfasdfasdfasdfqwerqwefa]

Is there a way to control the specific cell in excel that the imported table starts at in the below VBA code? Other than that this code has worked perfectly for me.

Hi again sopranour,

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.

 

[Story11]

This posting is in response to a question by Mattert. He asked:

"You indicated that the VBA code could simply be altered to apply to docs with multiple (181 in my case) tables. (identical format, seperated by page breaks)"

Here is a slight modification of the code to show how it can read multiple tables. This code just reads from two cells in each table and writes the result to the active worksheet. It loops through up to 10 tables (table indices 1 to 10), but you can modify the code to go from tables 1 to 181, 20 to 50, or whatever. Here's the code.

Sub ImportWordTables()

'Imports cells (3,2) and (4,2) from Word document Tables 1-10

   Dim wdDoc         As Word.Document
   Dim wdFileName    As Variant
   Dim TableNo       As Integer  'number of tables in Word doc
   Dim iTable        As Integer  'table number index
   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 > 10 Then
         TableNo = 10
      'Else TableNo is actual number of tables between 1 and 9
      End If
      
      Range("A1") = "Table #"
      Range("B1") = "Cell (3,2)"
      Range("C1") = "Cell (4,2)"
               
      For iTable = 1 To TableNo
         With .tables(iTable)
            'copy cell contents from Word table cells to Excel cells in column B and C
            Cells(iTable + 1, "A") = iTable
            Cells(iTable + 1, "B") = WorksheetFunction.Clean(.cell(3, 2).Range.Text)
            Cells(iTable + 1, "C") = WorksheetFunction.Clean(.cell(4, 2).Range.Text)
         End With
         Next iTable
   End With
   
   Set wdDoc = Nothing
   
End Sub

I hope Mattert and others will find this helpful.

Damon

Hello!
I know this post is old but I need your help.
I don't know whether you can help me rewrite the code to something like what are in the files attached to this reply. I'll appreciate it if you can be of help.
Please save my soul...
Thank you in advance.

Name	Test Person 1
Age	25
Hired	Yes

<tbody>
</tbody>

Name	Test Person 2
Age	26
Hired	No

<tbody>
</tbody>

Name	Test Person 2
Age	26
Hired	No

<tbody>
</tbody>

The way I want it in Excel is shown below...


[TABLE="width: 320"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Age[/TD]
[TD]Hired[/TD]
[/TR]
[TR]
[TD]Test Person 1[/TD]
[TD]25[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Test Person 2[/TD]
[TD]26[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Story11]

This posting is in response to a question by Mattert. He asked:

"You indicated that the VBA code could simply be altered to apply to docs with multiple (181 in my case) tables. (identical format, seperated by page breaks)"

Here is a slight modification of the code to show how it can read multiple tables. This code just reads from two cells in each table and writes the result to the active worksheet. It loops through up to 10 tables (table indices 1 to 10), but you can modify the code to go from tables 1 to 181, 20 to 50, or whatever. Here's the code.

Sub ImportWordTables()

'Imports cells (3,2) and (4,2) from Word document Tables 1-10

   Dim wdDoc         As Word.Document
   Dim wdFileName    As Variant
   Dim TableNo       As Integer  'number of tables in Word doc
   Dim iTable        As Integer  'table number index
   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 > 10 Then
         TableNo = 10
      'Else TableNo is actual number of tables between 1 and 9
      End If
      
      Range("A1") = "Table #"
      Range("B1") = "Cell (3,2)"
      Range("C1") = "Cell (4,2)"
               
      For iTable = 1 To TableNo
         With .tables(iTable)
            'copy cell contents from Word table cells to Excel cells in column B and C
            Cells(iTable + 1, "A") = iTable
            Cells(iTable + 1, "B") = WorksheetFunction.Clean(.cell(3, 2).Range.Text)
            Cells(iTable + 1, "C") = WorksheetFunction.Clean(.cell(4, 2).Range.Text)
         End With
         Next iTable
   End With
   
   Set wdDoc = Nothing
   
End Sub

I hope Mattert and others will find this helpful.

Damon

Hello!
I know this post is old but I need your help.
I don't know whether you can help me rewrite the code to something like what are in the files attached to this reply. I'll appreciate it if you can be of help.
Please save my soul...
Thank you in advance.

Name	Test Person 1
Age	25
Hired	Yes

<tbody>
</tbody>

Name	Test Person 2
Age	26
Hired	No

<tbody>
</tbody>

The way I want it in Excel is shown below...


[TABLE="width: 320"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Age[/TD]
[TD]Hired[/TD]
[/TR]
[TR]
[TD]Test Person 1[/TD]
[TD]25[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Test Person 2[/TD]
[TD]26[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[TudorSmith]

Hello All. New here so forgive me for adding to this old post.

I have been tasked with importing word documents into Excel for MI Purposes. Using the thread above has been helpful as I've been able to use the VBA to import the data from the table in the word document.

My issue us that some of the cells in the Word table contain data from a user selection of a combo box. All data that is free form text entered gets successfully imported into my Excel worksheet. All values from the combo boxes in the word table are importing blank. Typical values expected to be imported might be "1. Very Satisfied", "2. Satisfied" etc.

Any ideas how I might be able to import the value the user has selected in the word document?

All/any help is appreciated!

Cheers

Tudor

 

[mahronec]

This is an old thread but I thought I'd ask here. I import a word table into excel using a method similar to above. The one area where it breaks is with enters (line breaks) in the word table cells are not carried over to excel.

So a word table with the values
frank
jim
bob

becomes frankjimbob in excel. How do I ensure the enters are carried over? Or detect them and insert an alt-enter in excel each time?

Has there been a solution to this issue? I am currently trying to figure it out as well.

Thanks.

 

[Sniip]

Hi all,

please for help, I have problem with export cells from Word table. The problem is that word documents have lot of tables an I need to test every firs table if exist cell(2,3). How can I do it?


With wdDoc.Tables(TableNo1)
If .cell(2, 3).Range.Value Like "" Then ' Here I need to test if cell exist but how?
Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = ISO ' If cell doesnt exist then fill cell with information - NO CELL
GoTo Line1 'And go
End If
Cells(b, 6) = WorksheetFunction.Clean(.cell(2, 3).Range.Text) ' If cell exist i would like to get value of word cell
PoslZnak2 = Right(WorksheetFunction.Clean(.cell(1, 1).Range.Text), Len(WorksheetFunction.Clean(.cell(1, 1).Range.Text)) - InStrRev(WorksheetFunction.Clean(.cell(1, 1).Range.Text), ":"))
Cells(b, 8) = PoslZnak2
End With


it is just part of code, but I can not conntinue without it
When I use: .cell(2, 3).Range.Value Like ""
and cell doesn exist VBA give me back error information doesnt exist And stop
Thank you very much for help.

 

[KVignesh]

Hi Gay,

Yes this can be done with a VBA macro using Automation. Are you willing to entertain a macro solution?

I have done this in the past, and if you are interested will try to locate the code I used before.

Damon

Instead of asking for Table number, whether it is possible to get the table number by code using a keyword search and export the table to excel
Could you please help to solve this