Word tables via VBA to Access DB

[Dominik]

Hi NG,

I have been searching for a pretty long time to find a possibility to
import a table (or more) from Word 2002 into an existing Access 2002
db (attach data to table in db).

The tables contain many multilined cells, so I decided to use DAO or
ADO to copy every single cell and paste it into a table with VBA. I
tried different posted code, but neither worked out. Other postings
just say importing from word to access without creating csv files is
impossible. So I am a little confused.

Manually this copy-and-paste-thing works, so there must be a way to do
this in VBA. I'd be glad someone could post a link to a kind of howto?
Or just give a little piece of code that does so.

Thanks in advance,
Dominik

 

[John Nurick]

Hi Dominik,

You can do it with Automation, by fetching the contents of each cell in
each row of the WOrd table and putting them into the appropriate fields
in a recordset. What follows is untested air code and will need some
massaging to make it work:

Dim docD As Word.Document
Dim tblW As Word.Table
Dim rowW As Word.Row
Dim celW As Word.Cell
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim j As Long
Dim strCell as String

Set docD = GetObject("D:\Folder\File.doc")
Set tblW = docD.Tables(1)

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

For Each rowW in tblW.Rows
rsR.AddNew
For j = 0 to rsR.Fields.Count - 1
strCell = rowW.Cells(j+1).Range.Text

'dump end-of-cell character
strCell = Left(strCell, Len(strCell) - 1)

'you may also need to convert Word paragraph
'marks and hard line breaks into CR+LF so they
'display correctly in Access textboxes, and
'to replace tab characters with an appropriate
'number of spaces

rsR.Fields(j).Value = strCell
Next j
rsR.Update
Next 'row

rsR.Close
docD.Close False
Set rsR = Nothing
Set docD = Nothing
Set dbD = Nothing

 

[Dominik]

Hi John,

thanks for your reply. Newbie question: Is the code for Word or for
Access VBA? Sorry, to this point I used Word and Excel for VBA
programming.

In Word I get: "Runtime error 91" in line Set rsR =
dbD.OpenRecordset("TestVBA")
In Access: "Error during compiling: User defined typ not declared"
(translated from german) in Dim docD As Word.Document

Dominik
-------------------------------------------
Sub anothertry()

Dim docD As Word.Document
Dim tblW As Word.Table
Dim rowW As Word.Row
Dim celW As Word.Cell
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim j As Long
Dim strCell As String

Set docD = GetObject("c:\test.doc")
Set tblW = docD.Tables(1)

Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset("TestVBA")

For Each rowW In tblW.Rows
rsR.AddNew
For j = 0 To rsR.Fields.Count - 1
strCell = rowW.Cells(j + 1).Range.Text

'dump end-of-cell character
strCell = Left(strCell, Len(strCell) - 1)

'you may also need to convert Word paragraph
'marks and hard line breaks into CR+LF so they
'display correctly in Access textboxes, and
'to replace tab characters with an appropriate
'number of spaces

rsR.Fields(j).Value = strCell
Next j
rsR.Update
Next 'row

rsR.Close
docD.Close False
Set rsR = Nothing
Set docD = Nothing
Set dbD = Nothing


End Sub

 

[Dominik]

Uhh,

my fault. I included ADO (although its DAO, right?) and removed it now
it seems to work, except for some character errors. Strange behaviour!
So code is for word and almost working.

Thank you,
Dominik

 

[John Nurick]

I wrote that code with the idea it would run in Access (after ensuring
that the VBA project included references to the DAO and Word object
libraries. But with a bit of modification it will run equally well in
Word.

Yes, it's DAO, which is still usually the simplest and most flexible way
of accessing data in .mdb files from other applications.