How to use VBA to read entries from a Word file into Excel

[Robin]

I really really need help on this and any input would be highly
appreciated:
I am doing a research project and recently got a data file. However,
this data file is in word format and needs to be transformed into excel
for analysis. Each entry of the data file is some basis information
about a company and the file is laid out as follows:

ABC Company
123 Some Rd., East City, CA 76543, 223-345-4567
A local leader in electronics manufacturing

DEF Company
456 Good St., West City, CA 90987, 209-876-7654
Had a new CEO this year and is planning to open an office in LA

GHI Company
789 Lucky St., South City, CA 67887, 321-876-0987
Going to close in 3 months
.......

I believe that there should be a way to work this out with VBA but I am
not familiar with it. I would not mind spending some time to learn it
but it would really help if somebody could give me some leads, like a
template or something.

Many many thanks!

Robin

 

[Howard Kaikow]

You'll need to learn VBA programming.

For Word, see the list of Word VBA books at
http://www.standards.com/index.html?WordVBABooks

I'd suggest starting with Steve Roman's Writing Word Macros.

For Excel, I'd suggest starting with John Walkenbach's Power Programming
book, see http://www.j-walk.com/ss/books/index.htm

 

[Robin]

Howard,

I know that I need to learn VBA to solve this but the problem is that I
do not have enough time to start from the very begining. I do have some
very basic knowledge about VBA and if you could kindly give me
something like a template, it would be much easier for me to start
programing.

Many many thanks!

Robin

 

[Howard Kaikow]

Howard,

I know that I need to learn VBA to solve this but the problem is that I
do not have enough time to start from the very begining. I do have some
very basic knowledge about VBA and if you could kindly give me
something like a template, it would be much easier for me to start
programing.

See http://www.standards.com/index.htm;?HowardKaikowServices

 

[Kodeworks]

Robin

All you have to do is record a macro in Word that will translate your
row-oriented data into a Word table, then copy and paste the table into
Excel.

This is what you have to do.

1. Open your original Word data file (make a backup copy first!)
2. Open a new blank document and create a table with as many columns
as there are rows in each record. From your sample data, it looks like
you need 3 columns. Make sure the table has at least as many rows as
you have records. Let's call this the Table document. Position the
cursor in the first column of the first row.
3. Go back to the Original document, position the cursor at the
beginning of the first line of the first record. Start the macro
recorder and save the macro in the Original document. Give it shortcut
key if you like.
4. Highlight the entire first line of the record only using the
keyboard (Shift-End) and cut it (Ctrl-X). The second line now moves up
to become the first line.
5. Switch to the Table document and paste (Ctrl-V). Hit the
right-arrow key to move one column to the right.
6. Switch back to the Original document and repeat steps 4 and 5 for
each row of the record.
7. When the last line in a record has been pasted into the last
column, use the arrow keys to move one cell down and three cells down
to the first column in the new row.
8. Switch back to the Original document and Stop the macro recorder.
9. The macro would have faithfully recorded your transfer of a single
record in the Original document to a single row in the Table document
with the cursors in both documents positioned to begin the transfer for
the next record.
10. Run the macro you recorded for each record.

The key to this technique is that the data in the Original file is
consistent, ie, same number of rows for each record. Also, we ALWAYS
use the keyboard to move the cursor and cut-and-paste and never use the
mouse reposition the cursor so that the recorder always postions the
cursor correctly. If you make a mistake during the recording, it's
best to start again.


Sunil Jadwani
www.kodeworks.com
Business Automation Solutions