Solution: Import data from Word to Access

[noccy]

Hello!

I have made some posts here about Importing data from a word table to an access table.

I finally came up with this solution and I'm posting it for other who might have the same problems:

Ok, here is how I did it. Thought it might be ok to post the code if anyone has the same problem =)

I am new to vb/vba, so plz dont look at the way the code is set up =)



code:--------------------------------------------------------------------------------

Private Sub cmdImportWord_Click()

Dim appWord As Word.Application
Dim doc As Word.Document
Dim strDocName As String
Dim App As DAO.DBEngine
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Mydb As String


strDocName = "c:\test.doc"


Set appWord = CreateObject("Word.Application")
appWord.Visible = False
Set doc = appWord.Documents.Open(strDocName)


Set Mydb = CurrentDb.Name

Set App = New DAO.DBEngine
Set db = App.OpenDatabase(Mydb)
Set rst = db.OpenRecordset("tblTest", dbOpenTable)



Dim MyArray(1000, 2) As Variant
Dim i As Integer
Dim j As Integer
Dim tblOne As Word.Table
Dim intChar As Integer
Dim intRows As Integer



'The table I want to get data from is the third table in the Word document

Set tblOne = doc.Tables(3)
Set intRows = doc.Tables(3).Rows.Count


For i = 2 To intRows


With rst
.AddNew



For j = 1 To 1

'To get rid of the character that ends the word cell, I had to count the charachters in each cell, and take away the last one

intChar = tblOne.Cell(i, j).Range.Characters.Count
MyArray(i, j) = Left(doc.Tables(3).Cell(i, j), intChar - 1)
Debug.Print MyArray(i, j)

rst("Field1").Value = MyArray(i, j)

Next j

For j = 2 To 2
intChar = tblOne.Cell(i, j).Range.Characters.Count
MyArray(i, j) = Left(doc.Tables(3).Cell(i, j), intChar - 1)
Debug.Print MyArray(i, j)

rst("Field2").Value = MyArray(i, j)

Next j


.Update

End With


Next i


rst.Close

Set rst = Nothing
Set db = Nothing
Set App = Nothing


doc.Close
appWord.Quit

MsgBox "Data imported from " & strDocName


End Sub

--------------------------------------------------------------------------------



Feedbacks appreciated =)

noccy