473,856 Members | 1,569 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

lots of Word tables to access Table?

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).T ables(1).Rows(1 ).Cells(2)) 'customer name
Cells(R, 3) = (Documents(1).T ables(1).Rows(4 ).Cells(2)) 'customer
contact
Cells(R, 4) = (Documents(1).T ables(1).Rows(1 ).Cells(4)) 'from
Cells(R, 5) = (Documents(1).T ables(1).Rows(2 ).Cells(2)) 'fax number
Cells(R, 6) = (Documents(1).T ables(1).Rows(3 ).Cells(4)) ' date
Cells(R, 7) = (Documents(1).T ables(1).Rows(4 ).Cells(4)) ' Sales rep
R = R + 2
Cells(R, 2) = (Documents(1).T ables(2).Rows(2 ).Cells(1)) ' Item Number
Cells(R, 3) = (Documents(1).T ables(2).Rows(2 ).Cells(2)) ' Part number
Cells(R, 4) = (Documents(1).T ables(2).Rows(2 ).Cells(3)) ' Description
Cells(R, 5) = (Documents(1).T ables(2).Rows(2 ).Cells(4)) ' Qty
Cells(R, 6) = (Documents(1).T ables(2).Rows(2 ).Cells(5)) ' Price

All ideas are welcome
Phil

End Sub
Nov 12 '05 #1
3 4318
On 17 Oct 2003 16:27:42 -0700, ph*********@wal ter-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.Applicatio n
set objWord = New Word.Applicatio n
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).T ables(1).Rows(1 ).Cells(2)) 'customer name
Cells(R, 3) = (Documents(1).T ables(1).Rows(4 ).Cells(2)) 'customer
contact
Cells(R, 4) = (Documents(1).T ables(1).Rows(1 ).Cells(4)) 'from
Cells(R, 5) = (Documents(1).T ables(1).Rows(2 ).Cells(2)) 'fax number
Cells(R, 6) = (Documents(1).T ables(1).Rows(3 ).Cells(4)) ' date
Cells(R, 7) = (Documents(1).T ables(1).Rows(4 ).Cells(4)) ' Sales rep
R = R + 2
Cells(R, 2) = (Documents(1).T ables(2).Rows(2 ).Cells(1)) ' Item Number
Cells(R, 3) = (Documents(1).T ables(2).Rows(2 ).Cells(2)) ' Part number
Cells(R, 4) = (Documents(1).T ables(2).Rows(2 ).Cells(3)) ' Description
Cells(R, 5) = (Documents(1).T ables(2).Rows(2 ).Cells(4)) ' Qty
Cells(R, 6) = (Documents(1).T ables(2).Rows(2 ).Cells(5)) ' Price

All ideas are welcome
Phil

End Sub


Nov 12 '05 #2
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",dbO penDynamic '--just some type that allows adding
records
rs.AddNew

for intCell = 1 to 7
rs.Fields(intCe ll)=xlsFile.Cel ls(R,intCell)
next intCell

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

rs.Fields("Tabl eFieldName") = 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,Colum n),
you could just iterate through it by doing something like:

For MyColumn = 1 to Table.Rows.Coun t
For MyRow = 1 to Table.Columns.C ount
'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.Coun t-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!
Nov 12 '05 #3
pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.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",dbO penDynamic '--just some type that allows adding
records
rs.AddNew

for intCell = 1 to 7
rs.Fields(intCe ll)=xlsFile.Cel ls(R,intCell)
next intCell

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

rs.Fields("Tabl eFieldName") = 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,Colum n),
you could just iterate through it by doing something like:

For MyColumn = 1 to Table.Rows.Coun t
For MyRow = 1 to Table.Columns.C ount
'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.Coun t-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.
Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
12384
by: Tom Dauria | last post by:
What I am trying to do is write a resume into a word document from information in an Access database. I have been using bookmarks and inserting table results into the document and so far it's working but I have run into a problem. The final section of the resume deals with Experience which is subgrouped by Market Segments and then experience. What I want it to look like is
41
6160
by: Ruby Tuesday | last post by:
Hi, I was wondering if expert can give me some lite to convert my word table into access database. Note: within each cell of my word table(s), some has multi-line data in it. In addition, there is one row containing picture(s) as well. So far, what I did is doing it manually for each word docs I have. Select Table Convert Table to Text(I use ^ character for delimiter)
8
3544
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At the moment the printed output is usually going to Word. It's turning into an unholy mess, because I'm having to prepare umpteen different Word templates, and the queries that drive them, depending on what events a course has.
1
4966
by: svdh | last post by:
I have posed a question last saturday and have advanced alot in the meantime. But I am still not there Problem is that I try to merging various fields from various tables in one document in Word 1. Query..I want to keep the fields seperatred. I do not want to sent on field with all accumulated languages from one person to Word. Each language should appear in the document in a separate cell Cross tables are not delivering the result I...
4
3373
by: svdh2 | last post by:
Dear All, I have lately strugled more and more with Access, what started as a simple database has brought me to the fundaments of Access. I need to transfer fields from various tables to a word-document to make a decent report (transferring a report from access to word means a lot of loss on lay out, which I find a clear showcase of microsoft's socalled software quality).
4
12458
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is this: can Access create the document and place it as an OLE object to the relevant table? Any help is greatly appreciated. Ricky
14
2913
by: awayne | last post by:
I am working with MS VB 6.5. I am putting together a MS Access (MS Access 2000) database for work to keep track of the projects and their status that we've done. I use MS Access to run a "Make-table query" that sorts by Ascending date (date is (Field(5) for coding purposes) and then use the information in the table to create the table in a word document. The query also captures beginning and ending dates so people can create a report that covers...
2
9280
by: Mo | last post by:
I am trying to produce an Access report which behaves like a two column table in Word. In other words each cell in column 1 contains a label and each cell in column 2 contains data from a different text field from a query and the cells expand or contract according to the size of the text field. I have tried "Can Shrink" but this leaves gaps and "Can Grow" but this seems to cause the cells to overlap and then Access throws page breaks...
3
3196
by: Robertf987 | last post by:
Well, I think I've described what I want to do in the title here. In the database, I have two main tables that contain the main data for the database. One for group expenditures, another for application details. I have a query for each of them to copy all the contents of the table into a new table, then delete all the data from the main table. I need to do this becasue once a year the data needs to be removed, ready for putting in new data. I...
0
9765
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10700
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10795
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10390
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9536
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7098
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5763
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4177
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3203
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.