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

[drsudduth]

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,

 

[Domenic]

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

[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]

 

[Domenic]

I forgot to mention that you'll need to set a reference by selecting...

Visual Basic Editor > Tools > References > Microsoft Word Object Library

 

[drsudduth]

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

 

[Domenic]

You're very welcome! Thanks for the feedback!

 

[rjm67]

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

 

[Logitron]

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!

 

[rjm67]

I didn't get any further with doing this in Excel, but the thought occurred that you could split the doc into single page ones and do it that way. It's a bit of a fudge, but here's some info on splitting docs. Split 1 large MS Word file into multiple files - Microsoft Community

 

[CDxel2011]

This is exactly what I was after, fantastic bit of work!

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