Extract data from Word Document to Excel SpreadSheet

Question

I have a requirement to extract the value from a text document daily and write it to an Excel workbook. I am currently doing this manually, and this is the boundary line for the most efficient method for me.

• Using an Excel file, create a vba script and add links to any words.

2 Using the word, go to table "9. SPARE PARTS ..." (extracted example below - Appendix A) and read the daily use of Diesel (ltr) highlighted in red.

3. Record this value in the cell in the spreadsheet.

4. The date of this value is also the key, but it is stored in another part of the word document (Appendix B). Dates are also in the file name, but we trust the internal value more than the document document name. With knowledge of points 3 and 4, output the corresponding date in the next cell of the spreadsheet.

The table is displayed below, due to formatting, I can not send you the exact table, but I can send its values.

9.STOCKS (as of 00:01 on the day the report was issued). Stock Exchange Daily Use Minimum Stock

Diesel (L)

390436 15012 25000

Nitrogen (mm)
35 1 19 Champion 1033 (totes)
15 1 4 Nexguard (Boilers) 4
0.25 4 x 200 ltrs

Appendix B: Beatrice Report Period: 00:01 - 24:00 August 10, 2010

If you have any doubts about my question, please return to me, I appreciate your efforts and would like to thank you in advance

Answer 1

here is some code using late binding (declare objects, not word.application, etc.). From Excel 2003 ,

• opens a WORD document
• looking for the string "minimum margin"
• moves the cursor a few lines / words further
• extends / selects the cursor WORD
• inserts this WORD selection into EXCEL

steps 2-5 are repeated for “Report Period:” (note that “:” is the word boundary, so we need to jump 8 words to the right to arrive at the date)

For WORD, I copied the text from your Q just as it is (no table, just text). If you use tables instead, you may need to play with units of various Move statements (for example, for unit:=12 cells unit:=12 ); the strategy remains the same: find the constant text, move the cursor to the final destination, expand the selection, create a range of words and drag.

Both elements are placed in the current cell in Excel and its right neighbor.

 Sub GrabUsage() Dim FName As String, FD As FileDialog Dim WApp As Object, WDoc As Object, WDR As Object Dim ExR As Range Set ExR = Selection ' current location in Excel Sheet 'let select the WORD doc Set FD = Application.FileDialog(msoFileDialogOpen) FD.Show If FD.SelectedItems.Count <> 0 Then FName = FD.SelectedItems(1) Else Exit Sub End If ' open Word application and load doc Set WApp = CreateObject("Word.Application") ' WApp.Visible = True Set WDoc = WApp.Documents.Open(FName) ' go home and search WApp.Selection.HomeKey Unit:=6 WApp.Selection.Find.ClearFormatting WApp.Selection.Find.Execute "Minimum Stock" ' move cursor from find to final data item WApp.Selection.MoveDown Unit:=5, Count:=1 WApp.Selection.MoveRight Unit:=2, Count:=2 ' the miracle happens here WApp.Selection.MoveRight Unit:=2, Count:=1, Extend:=1 ' grab and put into excel Set WDR = WApp.Selection ExR(1, 1) = WDR ' place at Excel cursor 'repeat WApp.Selection.HomeKey Unit:=6 WApp.Selection.Find.ClearFormatting WApp.Selection.Find.Execute "Period of Report:" WApp.Selection.MoveRight Unit:=2, Count:=8 WApp.Selection.MoveRight Unit:=2, Count:=3, Extend:=1 Set WDR = WApp.Selection ExR(1, 2) = WDR ' place in cell right of Excel cursor WDoc.Close WApp.Quit End Sub 

You can create a button and call this south from there, or associate GrabUsage () with a function key.

I commented on WApp.Visible = True , because during the production process you do not want the WORD to even appear, but you will need it for debugging and playback with cursor movements.

The disadvantage of late linking (and not using links to the Word library) is the hard coding of units (6 = history, 5 = string, 2 = word) instead of using Word enumerations, but sometimes I get OS crashes with early binding .... not very sexy but it seems to work.

The FileDialog object requires a link to the MS Office Office library. AFAIK is the standard in Excel 2003, but it's better to check than crash.

And I did not include the code to check if the elements were really found; I leave it for your creativity.

Hope this helps.