Importing from Word

Lusitan

Registered User.
Local time
Today, 13:22
Joined
Feb 17, 2004
Messages
34
Hello,

I have a file in word that is like this:

Name 1
Address 1
Phone 1
Postal Code 1
Comments 1

Name 2
Address 2
Phone 2
Postal Code 2
Comments 2

And so on, intil 300 and something. How can I import that into records to an Access table strtured more or less with the above mentioned fields?

Thank you.
 
Well, if you are comfortable with VBA, you can do this pretty easily provided you have some contextual cues in the file. But if VBA is not your thing, this is automatically and forever the wrong way to go 'cause it will just frustrate and confuse you to tears.

Going to try the VBA approach? Read up on some topics I will highlight in my reply. They will be either in the Word or Access Help files.

First, you need to be sure that you have references to Word. From a code window (module window), follow menu path Tools >> References and look for a reference to the Word object library. If it is not checked, check it.

Next, you need to write some VBA to create an APPLICATION OBJECT for Word. Once you do that, you need to either supply a file name (from a controlling form, though the form itself might be unbound), or using the FILEFIND object. The .FOUND property is a collection of files found matching whatever criteria you supplied. Presumably, you will use a form to trigger your code, though it could be done from a macro (not recommended).

Also open a RECORDSET to the table where you want to add records. Don't do anything else just yet. This could be a temp table or the real thing. Your call as to your confidence level.

OK, Open the file through the application object. This file becomes the ACTIVE DOCUMENT (Word Help on this one...). What happens is that this document's components are exposed via the Component Object Model that is part of AUTOMATION. You are about to start a loop here.

Every time someone ended the input line in your Word file with a hard carriage return (ENTER KEY) character, you started a new paragraph. If you actually have a blank line between two data groups, you will have a blank paragraph. Basically, the text of the line becomes available from something like

ActiveDocument.Paragraph(n).Text (or)
ActiveDocument.Paragraph(n).Range.Text

BUT look that up the Word Help for VBA - it will detail the actual structure of the paragraph and how to get the contents. It has been a while since I actually used that sequence. There are other properties such as (I think) .RANGE.COUNT to tell you the number of characters in the paragraph. And also, a .PARAGRAPHS.COUNT that tells you how many paragraphs you have in the entire document.

OK, so you read the paragraphs until you find a blank one. Save the lines as text strings. Now do whatever processing and identifying you need to do so that you know which string applies to each potential field in your table. In other words, if you have context clues to identify address 1 vs address 2 vs city vs state vs zip - like data labels - use them.

For the recordset, do an .ADDNEW to create a new record. Stuff each field in the record with one of the strings you captured from Word. For the recordset, do an .UPDATE

Now return to the top of the loop, reading through the Word document, repeating the loop until you reach the last paragraph. When you hit the last paragraph, you are done. Close the recordset and the application object. Exit your VBA module. ALWAYS close what you open - unless you like severe memory problems to plague and vex you constantly.
 
Cut n paste the doc into excel.
On the spare column on the side write in the numbers 1-7

1 - Name 1
2 - Address 1
3 - Phone 1
4 - Postal Code 1
5 - Comments 1
6 - Separator between records (groups of data)
7 - Blank lines you are not interested in

Then you can filter on 7 and erase those rows.
The remaining you can import into Access and manipulate from there.

Its an option, I used it on 300 records stored in Word, not elegant, and a little time consuming... But worked.

Alternatively you can vba code them by saving the lot into a plain text file and importing, or VBA coding to open the text file and import into the correct(ish) fields.

Vince
 
Thank you for your answers.

I'll give it a try and I'll let you know of the results.

Cheers
 

Users who are viewing this thread

Back
Top Bottom