MsWord Data to ADO

[Woo Mun Foong]

How can I import data from MsWord into Access.MDB
The data inside my MsWord file is neatly structured.
i.e 1 page break per record. and each records is neatly placed inside a
msword table.

ex :
____________________
| FieldName01: | Data01 |
|___________ |________|
|FieldName02: | Data02 |
|___________|________ |

--- page break
____________________
| FieldName01: | Data01 |
|___________ |________|
|FieldName02: | Data02 |
|___________|________ |

 

[John Nurick]

Hi,

There's no really simple way of doing this.

If every record contains the same fields in the same order, you can do
it like this:

1) delete the first column of each table, leaving single column tables
with just the data.

2) delete any paragraph marks that exist outside the tables.

3) use Edit|Replace to replace every remaining paragraph mark in the
document with a combination of characters that doesn't appear in the
data (e.g. %$%).

4) convert each of the tables to text. You should now have the contents
of each field as a single paragraph, with %$% wherever there used to be
a paragraph mark in the data and a page break between each record.

5) replace all the paragraph marks with tabs.

6) replace all the page breaks with paragraph marks. You should now have
a tab-delimited file that Access can import.

7) After importing, if necessary use an update query to replace all the
%$% with Chr(13) & Chr(10).

If the fields aren't always present and always in the same order, things
get more complicated and it will probably be necessary to write VBA code
to extract the data from the Word document and put it into Access.

 

[Woo Mun Foong]

I am sorry not to make myself clear.
What I'm looking for is a way to programatically (using VBA in AccessXP) to
open the MsWord file and run through the table and page break one by one and
retract its data.
Insert them into a table inside Access MDB.

Specifically, I like to know how to use Word Automation to navigate from Top
of File, detecting page breaks and tables in the process.

Thank You.
Woo

Hi,

There's no really simple way of doing this.

If every record contains the same fields in the same order, you can do
it like this:

1) delete the first column of each table, leaving single column tables
with just the data.

2) delete any paragraph marks that exist outside the tables.

3) use Edit|Replace to replace every remaining paragraph mark in the
document with a combination of characters that doesn't appear in the
data (e.g. %$%).

4) convert each of the tables to text. You should now have the contents
of each field as a single paragraph, with %$% wherever there used to be
a paragraph mark in the data and a page break between each record.

5) replace all the paragraph marks with tabs.

6) replace all the page breaks with paragraph marks. You should now have
a tab-delimited file that Access can import.

7) After importing, if necessary use an update query to replace all the
%$% with Chr(13) & Chr(10).

If the fields aren't always present and always in the same order, things
get more complicated and it will probably be necessary to write VBA code
to extract the data from the Word document and put it into Access.

..

 

[John Nurick]

Hi Woo,

Air code:

Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oTable As Word.Table
Dim oRow As Word.Row
Dim dbD as DAO.Database
Dim rsR As DAO.RecordSet
Dim strField As String
Dim strValue As String


Set oWord = CreateObject("Word.Application")
Set oDoc = oWord.Documents.Open("D:\Folder\File.doc")

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("TheTable")

For Each oTable in oDoc.Tables
rsR.AddNew
For Each oRow in oTableRows
strField = oRow.Cells(1).Range.Text
'dump paragraph and cell markers
strField = Left(strField, Len(strField) - 2)
strValue = oRow.Cells(2).Range.Text
strValue = Left(strValue, Len(strValue) - 2)
'This needs revision if you have non-text fields
'in the Access table
rsR.Fields(strField).Value = strValue.Value
Next 'row
rsR.Update
Next 'table

rsR.Close
Set rsR = Nothing
Set dbD = Nothing
oDoc.Close False
Set oDoc = Nothing
oWord.Quit
Set oWord = Nothing

 

[Woo Mun Foong]

Dear John,

The code work like magic.


Thank You.