# 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.



		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.


----------



## 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.



Damon Ostrander said:


> 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

Damon Ostrander said:


> 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.
> 
> 
> 
> 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.


NameTest Person 1Age25HiredYes

<tbody>

</tbody>

NameTest Person 2Age26HiredNo

<tbody>

</tbody>

NameTest Person 2Age26HiredNo

<tbody>

</tbody>
The way I want it in Excel is shown below...



NameAgeHiredTest Person 125YesTest Person 226No

<tbody>

</tbody>


----------



## Story11

Damon Ostrander said:


> 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.
> 
> 
> 
> 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.


NameTest Person 1Age25HiredYes

<tbody>

</tbody>


NameTest Person 2Age26HiredNo

<tbody>

</tbody>
The way I want it in Excel is shown below...



NameAgeHiredTest Person 125YesTest Person 226No

<tbody>

</tbody>


----------



## 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

sark666 said:


> 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

Damon Ostrander said:


> 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


----------



## 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


----------



## radas

Does anyone know how to copy the table without losing any formatting. When I use above macro, I can copy all cell data but I am loosing format (Like bullet points, page breaks and paragraph breaks). Anyone have modified code to NOT to lose any formatting? TIA.


----------



## oldbrewer

would you want the bullet point and its associated text in one cell,  or the bullet in one cell the text in another

it may help if you explain why you need to do this  as WORD is for words  and EXCEL is for calculations  [broad simplification]


----------



## radas

Thank you for quick response.

My word table has Text with bullet points.

Ex: 
This is a text paragraph

Text 1
Text 2
Text 3

I want everything in one cell without losing formatting.



oldbrewer said:


> would you want the bullet point and its associated text in one cell,  or the bullet in one cell the text in another
> 
> it may help if you explain why you need to do this  as WORD is for words  and EXCEL is for calculations  [broad simplification]


----------



## oldbrewer

a word table is quite similar to a block of excel cells

what are you going to do with it all when it is in excel

(this will help people to help you)


----------



## radas

I am trying to copy about 100 tables of requirements from word doc to Excel. With above code I can copy those requirements to excel, but I am losing the formatting (Not all columns, in word, have formatting)



oldbrewer said:


> a word table is quite similar to a block of excel cells
> 
> what are you going to do with it all when it is in excel
> 
> (this will help people to help you)


----------



## oldbrewer

I repeat - what is the driving requirement to copy the tables into Excel ?


----------



## radas

I want to use excel for my estimates and play with numbers for each requirement.
Hope that helps to understand why I want to copy from doc to excel


oldbrewer said:


> I repeat - what is the driving requirement to copy the tables into Excel ?


----------



## oldbrewer

ok - if one "cell" of word table contains  apples $3.50 per kilo
it will not be straightforward to get at the $3.50

can we see a few rows of a typical word table
(highlight it,  edit copy, and paste into the reply box here)


----------



## TrailRunner

oldbrewer said:


> ok - if one "cell" of word table contains apples $3.50 per kilo
> it will not be straightforward to get at the $3.50
> 
> can we see a few rows of a typical word table
> (highlight it, edit copy, and paste into the reply box here)



Hi - I am new to this forum and have found it very helpful.  I am following up on this thread as I have a similar challenge.  I am importing tables from a word doc to excel (requirements in a BRD) in order to bulk upload them into HP ALM.  The table has 3 columns - ID, Function, Description.  The description may have text formatting and .jpg/.png that I would like to retain. Can you offer assistance with this?


----------

