Word 2007 table info to Excel worksheet

I have several hundred Word 2007 files that are comprised of 5 tables with the same information. (Actually the fields are the same but the information may be filled in for that field on one document and blank for that same field on another.) I am trying to figure out a way to import the data from these fields into an Excel 2007 worksheet. I have tried converting the tables to text and saving as a delimited file which I imported into Excel and I have tried doing a cut and paste. Either I get nothing at all or all the fields end up in one column and multiple rows. I need each Word document to be a single record with one row and multiple columns. I am working with files from Office 2007 and 2010.
jkiddaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wchhCommented:
Just Copy from word (table) and paste to excel...
jkiddaAuthor Commented:
I tried that but it doesn't break up the parts of the table in the proper columns. I have 1800 of these documents so I was hoping not to have to do 5 copy and paste from the 1800 documents. The tables contain the field name and the info that goes in the field. I need to extract the information without the field names. If I can extract them all but in separate columns, I can delete the field names out to clean it up.
wchhCommented:
Any sample of word table?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

MWGainesJRCommented:
Can you post a sample doc file
jkiddaAuthor Commented:
Here is a sample. I have highlighted the fields that would be column headers. The file is saved as a Word 97 document. I can use them as is or convert them to Word 2007 or 2010. They will ultimately end up as an Excel 2007 file.
Admiral-BaybrookGateway.doc
Helen FeddemaCommented:
If (and only if) the Word docs are identical in layout, you could make an Excel workbook template with the appropriate column headings, and fill the data rows by selecting the appropriate table cell for each column, testing it for containing data, and if it does contain data, transfer it to the Excel cell.  This would be done using Automation code to work with the Excel object model.

I don't have any code samples illustrating transferring data from Word tables to Excel, but see my Code Sample #8 for transferring Word table data to Access, and Access Archon #200 for transferring Access data to Excel. Here are links for downloading them:

http://www.helenfeddema.com/Files/accarch200.zip
http://www.helenfeddema.com/Files/code8.zip

The general process would be to iterate through the documents (say they are all in a specific folder, which you could select using the procedure below), opening each in turn and exporting its data to a newly created workbook (made from the Excel template).
Public Function SelectFolder() As String
'Requires Office XP (2002) or higher
'Requires a reference to the Microsoft Office Object Library
'Created by Helen Feddema 3-Aug-2009
'Last modified 3-Aug-2009

On Error GoTo ErrorHandler

   Dim strFolderPath As String
   Dim fd As Office.FileDialog
   Dim strPath As String

   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)

   'Set strPath to the folder you want to open initially
   strPath = "G:\Data"

   With fd
      .Title = "Browse for folder where _________ are located"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      '.InitialFileName = strPath
      If .Show = -1 Then
         strFolderPath = CStr(fd.SelectedItems.Item(1)) & "\"
      Else
         Debug.Print "User pressed Cancel"
         strFolderPath = ""
      End If
   End With

   SelectFolder = strFolderPath
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
Another approach would be to create a Word macro, run from a toolbar button in the Word Template (hopefully these docs are all made from a template), which would export data from that document to a new Excel workbook.
jkiddaAuthor Commented:
The Word documents are all identical in form but the were not created using a template. I like the idea of your suggestion about using the automation code to fill in data under the column headings in the workbook. I just have to play with some of the code to see if I can pull that off. I'm only vaguely familar with using automation code. I will download the code and play with it. I guess I could also load it into Access and then dump it to Excel from there. It's an extra step, but it beats the alternative.
Helen FeddemaCommented:
If you are familiar with VBA, you should be able to cobble together something that will work, using the code from the code sample and article.
jkiddaAuthor Commented:
Helen,

Thank you for sharing the code examples with me. After looking them over, I realized it has been way to long since I coded in VB. I'm not even sure where to begin to build the code needed to move my Word table data into Excel. I appreciate your help and suggestion but will have to continue to look for another way to import the data.
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.