lots of Word tables to access Table?

[Phil Rutter]

Hello All,
I have about 700 word documents that have 2 tables one is static
4 colums x 5 rows the other is 5 colums x rows ranging from 2 to 100
what i wolud like to do is open the word doc.
import the first word table then import the second word table close
word doc
open next word doc and repeat process.
i am able to import one set of data currently into an excel spread
sheet
but how can i loop through all the documents and import them all into
one access table?
the below code works only on one doc and is designed for excell
my current code looks like this

Sub Listfiles()
'Open "F:\ACI-03-0760.DOC" For Input As #1
R = 1
Cells(R, 1) = "Quote Name"
Cells(R, 2) = "To"
Cells(R, 3) = "Contact"
Cells(R, 4) = "From"
Cells(R, 5) = "Fax"
Cells(R, 6) = "Date"
Cells(R, 7) = "sales Rep"
Range("a1:z1").Font.Bold = True
R = R + 1
Cells(R, 2) = (Documents(1).Tables(1).Rows(1).Cells(2)) 'customer name
Cells(R, 3) = (Documents(1).Tables(1).Rows(4).Cells(2)) 'customer
contact
Cells(R, 4) = (Documents(1).Tables(1).Rows(1).Cells(4)) 'from
Cells(R, 5) = (Documents(1).Tables(1).Rows(2).Cells(2)) 'fax number
Cells(R, 6) = (Documents(1).Tables(1).Rows(3).Cells(4)) ' date
Cells(R, 7) = (Documents(1).Tables(1).Rows(4).Cells(4)) ' Sales rep
R = R + 2
Cells(R, 2) = (Documents(1).Tables(2).Rows(2).Cells(1)) ' Item Number
Cells(R, 3) = (Documents(1).Tables(2).Rows(2).Cells(2)) ' Part number
Cells(R, 4) = (Documents(1).Tables(2).Rows(2).Cells(3)) ' Description
Cells(R, 5) = (Documents(1).Tables(2).Rows(2).Cells(4)) ' Qty
Cells(R, 6) = (Documents(1).Tables(2).Rows(2).Cells(5)) ' Price

All ideas are welcome
Phil

End Sub

[Tom van Stiphout]

On 17 Oct 2003 16:27:42 -0700, ph*********@walter-speedmax.com.au
(Phil Rutter) wrote:

Use the Dir function to enumerate all documents in a certain folder.
Then call your function for each file:
Sub ListFiles(byval strFileName as String)
Open strFileName For Input As #1

MUCH better than opening a Word Document the way I described above is
to use Automation.
dim objWord as Word.Application
set objWord = New Word.Application
etc.
You now have access to the Word object model, and you can access Word
tables with little effort.

-Tom.

Hello All,
I have about 700 word documents that have 2 tables one is static
4 colums x 5 rows the other is 5 colums x rows ranging from 2 to 100
what i wolud like to do is open the word doc.
import the first word table then import the second word table close
word doc
open next word doc and repeat process.
i am able to import one set of data currently into an excel spread
sheet
but how can i loop through all the documents and import them all into
one access table?
the below code works only on one doc and is designed for excell
my current code looks like this

Sub Listfiles()
'Open "F:\ACI-03-0760.DOC" For Input As #1
R = 1
Cells(R, 1) = "Quote Name"
Cells(R, 2) = "To"
Cells(R, 3) = "Contact"
Cells(R, 4) = "From"
Cells(R, 5) = "Fax"
Cells(R, 6) = "Date"
Cells(R, 7) = "sales Rep"
Range("a1:z1").Font.Bold = True
R = R + 1
Cells(R, 2) = (Documents(1).Tables(1).Rows(1).Cells(2)) 'customer name
Cells(R, 3) = (Documents(1).Tables(1).Rows(4).Cells(2)) 'customer
contact
Cells(R, 4) = (Documents(1).Tables(1).Rows(1).Cells(4)) 'from
Cells(R, 5) = (Documents(1).Tables(1).Rows(2).Cells(2)) 'fax number
Cells(R, 6) = (Documents(1).Tables(1).Rows(3).Cells(4)) ' date
Cells(R, 7) = (Documents(1).Tables(1).Rows(4).Cells(4)) ' Sales rep
R = R + 2
Cells(R, 2) = (Documents(1).Tables(2).Rows(2).Cells(1)) ' Item Number
Cells(R, 3) = (Documents(1).Tables(2).Rows(2).Cells(2)) ' Part number
Cells(R, 4) = (Documents(1).Tables(2).Rows(2).Cells(3)) ' Description
Cells(R, 5) = (Documents(1).Tables(2).Rows(2).Cells(4)) ' Qty
Cells(R, 6) = (Documents(1).Tables(2).Rows(2).Cells(5)) ' Price

All ideas are welcome
Phil

End Sub

[Pieter Linden]

Phil,
If I'm reading this right, I think this is going to be somewhat
complicated. You can access the tables collection in your Word file
without a problem and even cycle through the rows and columns pretty
easily. I think the 4x5 table I would process somewhat as you have
described. Say you open a recordset based on an *Access* table (don't
want to get confused here!). Then you could add the 20 variables from
the Word doc to the single record in Access. (Or some to child
tables... whatever you need.) Then the second table, you could just
loop through by using rows and columns collections of the Word table
object.

That's the "inner" loop (for each individual document). Now the outer
loop. If all your documents are grouped in folders, you can use the
Dir function to get all the names and then process them. you could
use something like the OpenFile API at www.mvps.org to prompt the user
for a path and then just start processing. You'd want to throw up....
no wait, I mean *show* a custom message form, and show the filename
that's currently being processed. If processing all the files is
going to take a while, you don't want the user thinking that the
computer is frozen, giving it the three-finger salute and corrupting
all your files.

Instead of where you have Cells(R,C) here, you would be appending to
an Access recordset... say something like:

rs.Open "TableName",dbOpenDynamic '--just some type that allows adding
records
rs.AddNew

for intCell = 1 to 7
rs.Fields(intCell)=xlsFile.Cells(R,intCell)
next intCell

Then when you get the the fields you have to match manually, you could
use something like:

rs.Fields("TableFieldName") = xlsFile.Cells(R,C)

The one thing I'd worry about is how "normalized" your Word/Excel data
is. If rows are not records, then you're going to have to probably
convert them in memory somehow. Say you define an array(Row,Column),
you could just iterate through it by doing something like:

For MyColumn = 1 to Table.Rows.Count
For MyRow = 1 to Table.Columns.Count
'Process Here
Next MyRow
Next MyColumn

sorry, don't know off the top of my head if rows and columns in Word
tables are 1 or zero based. If zero-based, just subtract 1...

For MyColumn = 0 to Table.Rows.Count-1

Oh, and if you're going to throw in a loop that's going to repeat
several hundred times, you definitely want to log all the processing,
so at least you can query it and not have to read it all. When you
run your import process, I would move the processed files to a new
directory - DirectoryA if successful, DirectoryB if not successful.
Then at least you know at a glance if everything's working, and if
not, you can just move them all in bulk or point to the failed
folder... (BrowseFolder API at Accessweb comes to mind for that...)

Okay, enough rambling, but I hope this gets you started. Have fun!

[Phil Rutter]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

Phil,
If I'm reading this right, I think this is going to be somewhat
complicated. You can access the tables collection in your Word file
without a problem and even cycle through the rows and columns pretty
easily. I think the 4x5 table I would process somewhat as you have
described. Say you open a recordset based on an *Access* table (don't
want to get confused here!). Then you could add the 20 variables from
the Word doc to the single record in Access. (Or some to child
tables... whatever you need.) Then the second table, you could just
loop through by using rows and columns collections of the Word table
object.

That's the "inner" loop (for each individual document). Now the outer
loop. If all your documents are grouped in folders, you can use the
Dir function to get all the names and then process them. you could
use something like the OpenFile API at www.mvps.org to prompt the user
for a path and then just start processing. You'd want to throw up....
no wait, I mean *show* a custom message form, and show the filename
that's currently being processed. If processing all the files is
going to take a while, you don't want the user thinking that the
computer is frozen, giving it the three-finger salute and corrupting
all your files.

Instead of where you have Cells(R,C) here, you would be appending to
an Access recordset... say something like:

rs.Open "TableName",dbOpenDynamic '--just some type that allows adding
records
rs.AddNew

for intCell = 1 to 7
rs.Fields(intCell)=xlsFile.Cells(R,intCell)
next intCell

Then when you get the the fields you have to match manually, you could
use something like:

rs.Fields("TableFieldName") = xlsFile.Cells(R,C

The one thing I'd worry about is how "normalized" your Word/Excel data
is. If rows are not records, then you're going to have to probably
convert them in memory somehow. Say you define an array(Row,Column),
you could just iterate through it by doing something like:

For MyColumn = 1 to Table.Rows.Count
For MyRow = 1 to Table.Columns.Count
'Process Here
Next MyRow
Next MyColumn

sorry, don't know off the top of my head if rows and columns in Word
tables are 1 or zero based. If zero-based, just subtract 1...

For MyColumn = 0 to Table.Rows.Count-1

Oh, and if you're going to throw in a loop that's going to repeat
several hundred times, you definitely want to log all the processing,
so at least you can query it and not have to read it all. When you
run your import process, I would move the processed files to a new
directory - DirectoryA if successful, DirectoryB if not successful.
Then at least you know at a glance if everything's working, and if
not, you can just move them all in bulk or point to the failed
folder... (BrowseFolder API at Accessweb comes to mind for that...)

Okay, enough rambling, but I hope this gets you started. Have fun!

Pieter,
thanks for all the information as i am pretty new to the coding thing
i tend to get myself lost.
thanks for the help.