Extracting data from Word Doc table to Excel

E-QCDA

Registered
I was given a task to extract data from a word doc table and insert it into an excel file for tracking $/graph purposes. There would be a total of 3 columns in the excel file. There are about a thousand or so of the individual word document forms that are filled out and saved electronically in a folder. I'm not quite sure how to go about getting this information a quicker way than going into each word doc and copy/paste out of the table into the excel spreadsheet. If anyone has any ideas of a formula or macro, I would greatly appreciate any help. Thanks!
 

Jen Kirley

Quality and Auditing Expert
Leader
Admin
This is an interesting question. I found a page titled How to Import Data From MS Word to MS Excel. I don't have big Word documents with tables to try it for myself. Do you want to try it and let us know if it worked for you? (Hint: Before doing anything, save these files under new names so you are experimenting with copies and not risking the real thing!)
 

Jean_B

Trusted Information Resource
My feeble to short-on-time guess: save all word files to PDF (PDF printer might do batches), combine all separate PDF's into one (Adobe Acrobat Pro function), import PDF into Excel (probably 365) using the "Get Data>>From File>>From PDF" option on the data tab.
Will probably only work if the structure of the data is identical across all files.
 

Raisin picker

Quite Involved in Discussions
I recently used ChatGTP to help me create VBA-Scrips for such tasks. But then, I have a little experience in programming myself, I just need reminders on how to create the code for the specific task.
I used to do a similar task with data from CSV files, but word should work as well, as long as the table inside the document can be identified automatically.

step 1: add the "developer tools" (or however they are called in english) to your excel menu band. It should be available in the selection menu, but unchecked by default.
step 2: record a macro (importing one file to excel via the excel dialogue, maybe test the optimal settings beforehand), and give it a short name and a short description (that can be done later, but don't forget)
step 2.5: open the macro in Excel, a new window will open with the code
step 3: add code to identify all files in the folder and cycle through them (a forum or AI can help), and replace the code that identifies the one file you used.
step 4: add code to identify the last line in your table so you can paste at the right location (a forum or AI can help) and replace the select-command.
step 5: test
step 6: correct
step 7: goto step 5

You can save the macro in the excel file, or in your own personal.xlsb.

Addendum: you can let the forum or AI do the whole script in one go (steps 2 to 4), you just need to define the task in sufficient detail. You still will have to test yourself.
 

John Predmore

Trusted Information Resource
There is a File Save option in MS Word to save a document as a Plain text file. Text format also strips out any graphics or format information. Sadly, this option saves the cells from a table with each cell on its own separate line. But it is fairly easy to write an Excel macro or use formulas to convert a column of numbers back into a 3-column table. There are also file format conversion utility programs that might be able to automate the procedure for many files and perform the conversion in batch, and maybe keep tables in tabular form. I want to focus on how to combine and input the text information from many files into Excel.

Once you have a large number of text files in a Windows folder, they can be concatenated together using the MS-DOS command COPY, which supports the use of wildcard characters. [There is also an XCOPY command with more options that works largely the same.] Open a MS-DOS command window with the Windows-key+R, then enter on the command line "cmd".

In the black MS-DOS session window, navigate to the folder where your text files are saved using the CHDIR command. Once in the desired folder, enter the command
COPY *.txt combined.csv
or something similar, which concatenates all files in the folder ending with filetype txt into a single combined csv file. The csv filetype saves a step because MS Excel will recognize it as a text file and open it into Excel using the text input wizard.
 

E-QCDA

Registered
I recently used ChatGTP to help me create VBA-Scrips for such tasks. But then, I have a little experience in programming myself, I just need reminders on how to create the code for the specific task.
I used to do a similar task with data from CSV files, but word should work as well, as long as the table inside the document can be identified automatically.

step 1: add the "developer tools" (or however they are called in english) to your excel menu band. It should be available in the selection menu, but unchecked by default.
step 2: record a macro (importing one file to excel via the excel dialogue, maybe test the optimal settings beforehand), and give it a short name and a short description (that can be done later, but don't forget)
step 2.5: open the macro in Excel, a new window will open with the code
step 3: add code to identify all files in the folder and cycle through them (a forum or AI can help), and replace the code that identifies the one file you used.
step 4: add code to identify the last line in your table so you can paste at the right location (a forum or AI can help) and replace the select-command.
step 5: test
step 6: correct
step 7: goto step 5

You can save the macro in the excel file, or in your own personal.xlsb.

Addendum: you can let the forum or AI do the whole script in one go (steps 2 to 4), you just need to define the task in sufficient detail. You still will have to test yourself.

I tried writing a VBA code for this issue and I must be doing something wrong. The below is what I wrote but I think I must have an error in it.

Sub Copy_Data_From_Multiple_WordFiles()

Dim FolderName As String
Dim FileName As String
Dim NewWordFile As New Word.Application
Dim NewDoc As New Word.Document

Application.DisplayAlerts = False
'Application.ScreenUpdating = False

FolderName = "Z:\Gabes PR Folder"
FileName = Dir(FolderName)

'Loop start

Do While FileName <> ""

Set NewDoc = NewWordFile.Documents.Open(FolderName & FileName)

NewDoc.Range(0, NewDoc.Range.End).Copy
Range("Table").PasteSpecial x1PasteValues

NewDoc.Close
NewWordFile.Quit

FileName = Dir()

Loop



End Sub

 
Top Bottom