get Word tables into Access..

[nycdon]

I'm trying to grab some data from Word docs to get into Access (or via
Excel). I don';t know Word model too well. Tried Import to Access, but
couldn;t find format that kept table intact.

Then thought record a Macro to select/copy/paste table into Excel, then
import to Access. (guess more used to going from excel to access).

With macro, had to key down to 1st table area, then pf8 to select table. VB
wrote..

Selection.MoveDown Unit:=wdLine, Count:=11
Selection.Tables(1).Select

I commented out first line, thinking unnecessary, as next line would just
select 1st occurence in Tables collection, but Error said "Requested member
of collection does not exist"..

how could i select the tables in the word doc?

...also, any other strategies in doing this clearly open to..

thx,
Don

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?bnljZG9u?=,

If someone asked me to do this, I'd be looking at ADO or DAO to put the table
information into the database.

I'd then have to consider how the Word table is constructed:
- does the document contain more than one table? If yes, do all tables, or
just certain ones have to be transferred?
- does it contain merged cells?
- do any cells contain paragraphs marks or line breaks?
- are there column headers? If yes, do they correspond to field names in
the database? Or do I have to rely on the order of the columns?
- is the data type of the columns guaranteed to match the data types of
the database fields?

I'm trying to grab some data from Word docs to get into Access (or via
Excel). I don';t know Word model too well. Tried Import to Access, but
couldn;t find format that kept table intact.

Then thought record a Macro to select/copy/paste table into Excel, then
import to Access. (guess more used to going from excel to access).

With macro, had to key down to 1st table area, then pf8 to select table. VB
wrote..

Selection.MoveDown Unit:=wdLine, Count:=11
Selection.Tables(1).Select

I commented out first line, thinking unnecessary, as next line would just
select 1st occurence in Tables collection, but Error said "Requested member
of collection does not exist"..

how could i select the tables in the word doc?

...also, any other strategies in doing this clearly open to..

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[nycdon]

Thanks Cindy - yes, with my current limited knowledge of Word object model,
thought i'd see if another way first

as per your notes on considerations..

The multiple tables in doc do all need to be transferred.
How can I tell if a cell has been merged?
Some 'memo' type Cells do contain paragragh/line breaks.
Yes, column headers - though do not correspond to Access table. Figured I
could map to applicable Access table once I get raw data into Access, using
Update or append queries. (much more astute in Access than Word)


how might I approach in ADO or DAO?

thanks,
Don

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?bnljZG9u?=,

The multiple tables in doc do all need to be transferred.
How can I tell if a cell has been merged?

If you don't know what you're going to be getting, then that can be very
tricky. Best, really, is to "walk" the table, counting how many cells in each
row / each column. Timewise, this is expensive...

Some 'memo' type Cells do contain paragragh/line breaks.

OK, this is a bit easier to handle.

Transferring tables: there are basically two approaches that can be used. One
is to "walk" the rows and columns. This is very slow.

Faster is to first convert the table to delimited text format (check out
Table/Convert/Table to text to get a feel for this). This can then be read
directly into a string variable, which can either be
- written to a file, then imported into the database

- "split" into an array for further processing (i.e. read into a record
set)

The tricky part is that you'd need to use Find/Replace in Word to replace all
the paragraph marks in the table with some unique character, so that when the
table is converted to text the structure is maintained. Word always uses a
paragraph mark (Chr(13) = vbCR) as the record delimiter). Once you have the
data in a string variable, you can use the Replace function to replace that
unique character with a vbCRLF (what Access uses for a new line).

Yes, column headers - though do not correspond to Access table. Figured I
could map to applicable Access table once I get raw data into Access, using
Update or append queries. (much more astute in Access than Word)

Yes, that would work, as long as you know the things to map. I would imagine,
if you know this, you should probably also know whether the table contains
merged cells?

how might I approach in ADO or DAO?

DAO is Access's native database "language". you'll find it discussed in most
books on Access programming and it's fully documented in the Access Language
Reference ("VBA Help"). Look up terms such as CurrentDatabase, Recordset,
Field and you should get the basics. You can also use it to run Queries
(Update or Append queries, for example); you can even create and run them
"on-the-fly" if you need something more dynamic.

ADO is the interface language for OLE DB; think of it as the successor to
ODBC. It's database-independent, meaning you can use the same code for any
database, the only thing that you'd need to change would be the connection
string for connecting to the database you want to use. ADO supports running
Update and Append SQL directly on the database (IOW not using a Query stored
in Access).

Given your background, unless you think the entire tool (database and Word
table conversion) would once be moved to something like SQL server, I'd say
DAO would be the better choice. My advice would be to ask in an ACCESS
newsgroup for detailed help on the syntax. I'm sure the folks there can point
you to articles and tutorials.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail