Extract data from mutiple word documents (table) to single excel spreadsheet

drsudduth

New Member
Joined
Nov 20, 2011
Messages
12
I would like to understand how to extract specific cells from multiple word documents containing a single table to excel spreadsheet - all in one spreadsheet ie. one table one line in the spreadsheet?

Thank you,
:)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Place all your relevant Word documents in a folder, change the specified folder in the macro accordingly, and try the following macro that will extract all cells from each table in every Word document to the active worksheet, starting at A2...

Code:
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] test()[/FONT]
 
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] oWord [COLOR=darkblue]As[/COLOR] Word.Application[/FONT]
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] oDoc [COLOR=darkblue]As[/COLOR] Word.Document[/FONT]
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] oCell [COLOR=darkblue]As[/COLOR] Word.Cell[/FONT]
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
[FONT=Courier New][COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR][/FONT]
 
[FONT=Courier New]Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=darkblue]Set[/COLOR] oWord = CreateObject("Word.Application")[/FONT]
 
[FONT=Courier New]sPath = "C:\Users\Domenic\Desktop\" [COLOR=green]'change the path accordingly[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=darkblue]If[/COLOR] Right(sPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] sPath = sPath & "\"[/FONT]
 
[FONT=Courier New]sFile = Dir(sPath & "*.doc")[/FONT]
 
[FONT=Courier New]r = 2 [COLOR=seagreen]'starting row[/COLOR][/FONT]
[FONT=Courier New]c = 1 [COLOR=seagreen]'starting column[/COLOR][/FONT]
[FONT=Courier New]Cnt = 0[/FONT]
[FONT=Courier New][COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Len(sFile) > 0[/FONT]
[FONT=Courier New]    Cnt = Cnt + 1[/FONT]
[FONT=Courier New]    [COLOR=darkblue]Set[/COLOR] oDoc = oWord.Documents.Open(sPath & sFile)[/FONT]
[FONT=Courier New]    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oCell [COLOR=darkblue]In[/COLOR] oDoc.Tables(1).Range.Cells[/FONT]
[FONT=Courier New]        Cells(r, c).Value = Replace(oCell.Range.Text, Chr(13) & Chr(7), "")[/FONT]
[FONT=Courier New]        c = c + 1[/FONT]
[FONT=Courier New]    [COLOR=darkblue]Next[/COLOR] oCell[/FONT]
[FONT=Courier New]    oDoc.Close savechanges:=[COLOR=darkblue]False[/COLOR][/FONT]
[FONT=Courier New]    r = r + 1[/FONT]
[FONT=Courier New]    c = 1[/FONT]
[FONT=Courier New]    sFile = Dir[/FONT]
[FONT=Courier New][COLOR=darkblue]Loop[/COLOR][/FONT]
 
[FONT=Courier New]Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=darkblue]If[/COLOR] Cnt = 0 [COLOR=darkblue]Then[/COLOR][/FONT]
[FONT=Courier New]    MsgBox "No Word documents were found...", vbExclamation[/FONT]
[FONT=Courier New][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]
 
[FONT=Courier New][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]
 
Upvote 0
I forgot to mention that you'll need to set a reference by selecting...

Visual Basic Editor > Tools > References > Microsoft Word Object Library
 
Upvote 0
Thank you for the hint, and of course the code! It worked like a charm and will save me a lot of time running my code individually!!!!!:)
 
Upvote 0
Hi,

I've tried this out today with some good success but I was wondering about the following problems:

I have some docs that are multiple pages, but the same table format on each page. Is there a way to either define the cells that you want to export, or have the import place each page of a table on a separate line. at the moment everything is on one line and in some cases it gets truncated for being too long.

Cheers
Ricahrd
 
Upvote 0
I have a similar issue as the previous poster. The data that is produced from the code provided by Domenic above is great, but it only produces data for the first table in each document (as originally intended). I've tried manipulating the code myself to extract data from all of the tables in each document, but I can't seem to get it right. Can anyone help with this? Again, I'd just like to modify the code, if possible, to extract data from all tables in each document in the folder.

Thanks for the help!
 
Upvote 0
This is exactly what I was after, fantastic bit of work! :)

Question, is there a way of hyperlinking to the orignal document?
 
Upvote 0

Forum statistics

Threads
1,224,889
Messages
6,181,605
Members
453,055
Latest member
cope7895

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