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

 

[Damon Ostrander]

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

 

[sopranoiam]

Damon, yes when you get the chance I would like it, because I get one of these type of things about once a month, those who don't know how to use Excel type something in Word and totally confuse it.

 

[Damon Ostrander]

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.

 

[oleg_v]

HI

IS IT POSSIBLE TO RETRIVE THE DATA FROM A SPECIFIC CELL AND NOT THE HOLE TABE?????


Oleg

 

[waterdog15]

This is a pretty handy macro - thanks! How do you modify it if you have an unknown amount of tables in various word files?

Kudo's to the developer(s) and contributors of this site - it is a HUGE (shouting to the mountain tops!) wealth of information and help!

Thanks,
Paul

 

[Damon Ostrander]

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

 

[sark666]

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?

 

[ankurjp]

Thanks Damon for the solution. Also need to know, how is it possible to include multiple tables (from the same word document) into a single Excel tab?

Request your help with this.

 

[lcohn]

I have a similar need to this thread and was hoping someone could help me out.
I have a file folder with 30 or more word document files.
Each word doc has several tables
I need to take table 1 (first table) from each document and past the information into an excel worksheet.
I would like to past all the tables into the same worksheet. I would also like to get the file name for each world file and past it in just above each copied table. I have tried changing the code above, but my changes never work.