Import Word Data

[LeAnn]

Hi,

I have an Access database that I need to automate importing data from a Word
document. The data is in a table. Is it possible to write code to do this?

The document has other header information and the table has a couple of
header rows. I have some code that does a similar thing for an excel
document but I haven't found much in terms of reference information
regarding importing from word other than saving as a text file. I don't
think I want to save as a text file because the output doesn't look very
usable (at least for my level of coding).

Any suggestions or reference info?
Thanks
LeAnn

 

[John Nurick]

Hi LeAnn,

There's no easy way of doing this, especially if there are multiple
header rows, merged cells, paragraph marks within table cells, and other
complications. (Without them, just save the table as a tab-delimited
text file<g>).

Perhaps the most reliable approach is to use Automation to open the Word
document, find the table, and iterate through its rows and columns.

Air code:

Dim oDoc As Word.Document
Dim oTbl As Word.Table
Dim oRow As Word.Row
Dim oCell As Word.Cell
Dim R as Long, C As Long
Dim rsR As DAO.Recordset


Set oDoc = GetObject("C:\mydoc.doc")
Set oTbl = oDoc.Tables(1)

Set oRow = oTable.Rows(1)
For Each oCell in oRow.Cells
'use oCell.Range.Text to get field names from
'row 1 and/or row 2
'and build a TableDef or CREATE TABLE statement
Next
'create the table
...

'Open the recordset

'populate it
For R = 3 to oTbl.Rows.Count
Set oRow = oTbl.Rows(R)
rsR.AddNew
For C = 1 to NumberOfFieldsInTable
rsr.Fields(C-1) = oRow.Cells(C).Range.Text
Next
rsr.Update
Next

...

 

[LeAnn]

Thanks John, I'll keep this example handy for future need. I decided to
require the users to use an Excel file. I have everything working except one
insert statement.
My code inserts a new record into the parent table and imports one cell
value into the employee code field. This works fine. I then open a
recordset to grab the ID field of the parent record just recreated so I can
relate the other child records that I'm importing from the Excel file (using
loop statements).

Using some debugging methods I am successful in grabbing the values from the
Excel files and storing them into variables. I use those variables in the
INSERT INTO statement. When I run the code, everything works but no child
records are insert. Here is my insert statement. Do I need to convert each
data value into the appropriate data type?

db.Execute "INSERT INTO tblWBCCount(ReqID, SmplNum, RBC, PRP, CodeNum,
LRDate, LRTime, UnitVol, CreatedBy, Created) " _
& "VALUES(" & lngReqID & ",'" & strSmpl & "'," & bolRBC & "," & bolPRP &
",'" & strCode & "', #" & dteLRDate _
& "# ,#" & dteLRTime & "#," & lngVol & ",'" & strUName & "', #" & Now & "#)"

I am not getting any run-time errors.

Thanks for your assistance
LeAnn

 

[John Nurick]

The first thing is a bit more debugging. Declare a variable

Dim strSQLInsertChild As String

then instead of your present
db.Execute "INSERT INTO...
do
strSQLChild = "INSERT INTO...
db.Execute strSQLChild, dbFailOnError

Set a breakpoint on the assignment
strSQLChild = "INSERT INTO...

and when the code breaks there use
? strSQLChild
in the Immediate pane. You can then inspect the SQL to make sure it's
correct - and even copy and paste it into the SQL view of a new query so
you can try it out.

 

[LeAnn]

Thanks John. The debugging techniques were what I needed. I discovered my
error. I was violating Primary Key integrity rules. Should have realized
that.

Thanks for your assistance!