MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need Excel to pull data from a Word document


Posted by Noir on January 15, 2002 5:31 AM

I have a Word document that has all data in columns as follows; Date, Desc, Category, Cost.

I need a formula that will allow me to go into the "category and cost columns" and paste that data into cells of my Excel sheet 1.

Can this be done considering the data is being pulled from a Word document instead of an Excel sheet?

Thx.
Noir


Posted by Damon Ostrander on January 15, 2002 10:08 AM

Hi Noir,

Yes, this can be done using Automation. There are several examples in the VBA helps. I would recommend that if your table in Word is columnar as your describe that you put it into a Word table rather than formatting it in columns using tabs. You can turn all the table lines off so that the fact that it is a table is not apparent. Having the data in a table enables you to easily find the data you want from Excel, since now the table is a Table object and can be referred to as, for example, Tables(1). In addition the cells of the table can be addressed by row and column (e.g., Tables(1).Cell(1,3) for row 1, column 3). Once you find the Word table cell you want to paste into Excel, pasting it in place is simple, e.g.:

[B4] = appWd.ActiveDocument.Tables(1).Cell(1,3).Range.Text

to put the text into the activesheet's cell B4.

The data can be found without using a table, but it is a bit more difficult.

I hope this helps.

Damon

Posted by Noir on January 16, 2002 5:29 AM

Thanks Damon (NT)