Transfer data from Word docs into Access?

  • Thread starter Thread starter CW
  • Start date Start date
C

CW

I have a couple of hundred standalone Word docs which are individual
membership certificates containing companies' names and addresses, the
certificate dates and numbers and a couple of other bits of info.
I now want to set up an mdb containing this info (and more besides, which I
can add once this fundamental data is in there).
Is there any way to extract the data from the Word docs other than opening
each .doc file, reading the info and keying it into Access?
Many thanks
CW
 
I do not know if your files are such that this will work but I open each Word
doc and save as .txt.
I then open first .txt and second. Select All of second, copy, and paste to
end of first. Continue opening .txt files and pasting to the first. Close
..txt.
I then use Excel to open the .txt file, add colunm for =Row() and Paste
Special - Values. Then sort and delete blank rows. Massage data so it
import easily into Access.
 
Thanks Karl - quite cunning and I have tried it and it does work, but it
would still take quite a while to copy/paste and import all the data and I'm
just hoping (admittedly, probably in vain) that there might be some other
solution which is a bit less labour-intensive!
Many thanks
CW
 
If you want to follow Karl's approach, you can write some VBA code in Word
to loop through the files in a directory and automate the copying. You can
then save the results as text. The following is just a sample - it loads the
text files with a BAS extension in a directory into the current document.
Public Sub LoadDir()

Dim TmpName As String
Dim DirName As String
Dim FileName As String

DirName = "C:\Users\John\Documents\NJI Notes\"
TmpName = DirName & "*.bas"
Debug.Print TmpName
FileName = Dir(TmpName)
Do While Len(FileName) > 0

TmpName = DirName & FileName

Selection.TypeText "FileName : " & FileName
Selection.TypeParagraph

Selection.InsertFile FileName:=TmpName


FileName = Dir
Loop

End Sub



The real problem will be identifying the relevant text you need.

John...
 
CW,

I provide help with Access, Excel and Word applications for a small fee. I
can write code that will go through your Word docs and extract the
information into Access. My fee would be very reasonable to do this for you.
It would well be worth the cost to avoid opening each .doc file, reading the
info and keying it into Access. If you are interested, contact me.

Steve
(e-mail address removed)
 
Steve said:
CW,

I provide help with Access, Excel and Word applications for a small fee. I
can write code that will go through your Word docs and extract the
information into Access. My fee would be very reasonable to do this for
you. It would well be worth the cost to avoid opening each .doc file,
reading the info and keying it into Access. If you are interested, contact
me.

Steve
Click to expand...

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP
 
Also CW,

There is a Knowledge Base article on the Microsoft website that describes
how to do what John has suggested. I used it a few years ago. And if all of
your data isn't in a table, you can probably create macros to clean up your
Word files.

David
 
Steve said:
CW,

I provide help with Access, Excel and Word applications for a small fee. I
can write code that will go through your Word docs and extract the
information into Access. My fee would be very reasonable to do this for
you. It would well be worth the cost to avoid opening each .doc file,
reading the info and keying it into Access. If you are interested, contact
me.

Steve
(e-mail address removed)
Click to expand...


So I guess you are planning on using the code I posted and then charge him
for it.

John... Visio MVP
 
I have a couple of hundred standalone Word docs which are individual
membership certificates containing companies' names and addresses, the
certificate dates and numbers and a couple of other bits of info.
I now want to set up an mdb containing this info (and more besides, whichI
can add once this fundamental data is in there).
Is there any way to extract the data from the Word docs other than opening
each .doc file, reading the info and keying it into Access?
Many thanks
CW
Click to expand...

Is there any way of identifying the bits of information you want to
cull from the Word documents such that you can do it through
automation? Something like "company name and address are always in
the first 3 paragraphs of the document" . If you can do that, then
reading the data should not be hard.... but without knowing that, it's
difficult to tell. Can you post a munged example? There's no need to
show real data - but a representative sample would help a lot.
 
You may be able to achieve what you are after by modifying the method in the
article in the following page of Greg Maxey's website

http://gregmaxey.mvps.org/Extract_Form_Data.htm

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.
 
Hi

I also would suggest if you know the position or there is any specific
phrase that identifies the info in the word document then you can simply
write a VBA code to open the word doc ,searh for the specific phrase and pull
the data that way.

Good luck
 
You can also do some DOS instead of copying and pasting the .txt files.
After files are saved as .txt click on the START button, click on Run....
Type CMD and press ENTER.
Use these commands to get to your drive --
cd\ moves you to root directory, most likely c:
cd p: to go to drive P.
cd My Documents to go to My Documents folder of the current drive.

When you are there use the copy command like this --
copy file1.txt + file2.txt + file3.txt file_All.txt

This places the content of each file in sequence in new file named
file_All.txt.

You can then open it with Excel or Access.
 
You can also use the doc command
DIR/B *.DOC > Wordfiles.TXT
This will create a text file that contains the name of the doc files in the
directory. This file can then be editedto add the file concatenation that
Karl mentioned.
This works great for text files, but I do not think you can concatenate Word
files. You can also add the /S switch to include sub directories.

John... Visio MVP
 
Back
Top