Convert Word to Excel

[Ammarbokhari]

Hi,
I have around 500 word files containing different quotations. (Cover page, Item description, and Pricing details in tabular format)
What I want to do is to convert those files to excel so that I can get all the data in a few sheets which I can summarize using different data functions.
Basically, I have made tables in my word quotation file, if I could only get those tables to excel that would be the most important part, other data are important too, but I want to compare prices that we have quoted in different projects to different clients etc (so table would do)
I don't want to go into each word file one by one (500 files would take a lot of time). So is there a utility or a software which I can use.
If I get the data in same layout as it is present in Word, that would be great. Or if I need to tweak it a little once it is in excel, that is also fine.
Hope I made my question clear.
Looking forward to hearing from you.
Thank You.

 

[Trevor G]

Do all the Word documents reside in a single folder location?

Do each of the documents have only a single table in them?

You can use Excel VBA Code to loop through the folder and open each document, select and copy the table, paste into Excel then close the document.

Something like this will open a document and copy a table to excel, test it to see if this will help. It is only one document for the moment.

Sub wordTables()
'Add reference to Word, Tools Menu > References > Microsoft Word
Dim wrdApp As Word.Application
Set wrdApp = CreateObject("Word.Application")
With wrdApp
.Visible = True
.Documents.Open "C:\Users\Trevor Glovwer\Documents\Title.doc" 'Change to location
.Selection.GoTo What:=wdGoToTable, Which:=wdGoToFirst, Count:=1, Name:=""
.Selection.Tables(1).Select
.Selection.Copy
End With
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Range("D16").Select

End Sub

 

[Ammarbokhari]

Do all the Word documents reside in a single folder location?

Do each of the documents have only a single table in them?

You can use Excel VBA Code to loop through the folder and open each document, select and copy the table, paste into Excel then close the document.

Something like this will open a document and copy a table to excel, test it to see if this will help. It is only one document for the moment.

Hi,
I don't have the files here for which I required this macro, but answer to your questions is as below:
All the files are not in single folder originally but I have placed them in one folder for convenience.
Yes, each document has a single table.

Will this macro loop through the folder and open files one by one? If so, it will save me 1 double click each file, or would it help me in some other way as well.
What I have done so far is, to convert each file by using "Total Excel Converter". But the data is not in the same layout as this software gives columns in place of spaces in word sheet (don't know why), and data then needs to be concatenated and sorted later on. (I have converted files to excel but have not tried concatenating yet).
I don't have knowledge about VBA, but if you want to help, you can, by creating a VBA code for following process:
If word is converted / saved to HTM format, then if you go to excel DATA tab and take data from website and paste the link to the HTM created from word file. The data comes into excel in far better layout then if you take the data directly from word file.
So, it is a two step process. First, convert the word file into HTM format (which can be done by using "Total Excel Converter") , and step 2 requires your help, where a code would be made to insert HTM files from one complete folder into separate sheets of excel.

You can record a macro for inserting one HTM sheet into excel, and then loop the process for all HTM files in that folder. (I don't know how to loop through a folder to repeat the process for all HTM files).
Hope I made my question clear.
I will try your code tomorrow on those files.
Looking forward to hearing from you.
Thank you.

 

[Trevor G]

I dont know the Converter you are looking to use.

Here is some code to open all documents in a folder you will have to do some editing to complete your task. If you include what I added in my earlier reply it will assist you.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>MyPath = "M:\Access Files\" <SPAN style="color:#007F00">'Change to your folder location</SPAN><br>MyFile = Dir(MyPath)<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.doc" <SPAN style="color:#00007F">Then</SPAN><br>Documents.Open MyPath & MyFile<br><SPAN style="color:#007F00">'Adapt the code I added earlier to open the documents</SPAN><br><SPAN style="color:#007F00">'Select and copy the table</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>