Copy Word Data to Access Database

[Belch]

Hi all,

I have created a Word template - when a user opens a document based on that template a form appears with info to fill in. When they fill it in and click OK the form populates a table on the document.

I've now been asked to create a database of these forms (well, the info that appears on them).
So basically I was wondering how to save data from the Word form into an existing Access database.
If someone could point me in the right direction in terms of what I should be looking for on the net, or if there is an article or kbase item on this site (I started looking but couldn't see anything) that'd be great - I understand it's not just a case of a few simple methods but more complicated than that, but I don't know where to start.

Thanks,

[Oorang]

I assume you have all the document in that same location, so it would probablly by doable (but not trivial) to use the FileSystem object too loop through the documents, reading and parsing each one as you go. To use the methods in VBA just set a reference to "Microsoft Scripting Runtime", "Microsoft Word". Here is the rough idea:
[VBA]Option Explicit
Sub Example()
'Microsoft Scripting Runtime Objects:
Dim FSO As Scripting.FileSystemObject
Dim MyFolder As Scripting.Folder
Dim CurrentFile As Scripting.File
'Microsoft Word Object Library Objects:
Dim MyDoc As Word.Document
Dim WordApp As Word.Application
Dim MyTable As Word.Table
'Microsoft DAO Objects:
Dim MyRecordSet As DAO.Recordset
Dim MyWorkspace As DAO.Workspace

'Other
Dim sValue As String
Dim Row As Long
Dim Column As Long
Dim FieldIndx As Long
Const DocExt_c As String = "doc"

Set FSO = New Scripting.FileSystemObject
Set WordApp = New Word.Application
Set MyFolder = FSO.GetFolder("C:\Test")
Set MyWorkspace = Access.DBEngine.Workspaces(0)
MyWorkspace.BeginTrans
Set MyRecordSet = CurrentDb.OpenRecordset("Addresses", dbOpenTable, dbAppendOnly)
For Each CurrentFile In MyFolder.Files
If VBA.StrComp(FSO.GetExtensionName(CurrentFile.ShortPath), DocExt_c, vbTextCompare) Then
Set MyDoc = WordApp.Documents.Open(CurrentFile.ShortPath, ReadOnly = True)
Set MyTable = MyDoc.Tables(1)
MyRecordSet.AddNew
FieldIndx = 0
For Row = 1 To 2
For Column = 1 To 2
MyRecordSet.Fields(FieldIndx).Value = CStr(MyTable.Cell(Row, Column).Range.Text)
FieldIndx = FieldIndx + 1
Next Column
Next Row
MyRecordSet.Update
End If
Next CurrentFile

End Sub

[/VBA]

[geekgirlau]

If you use Word forms (as opposed to a user form in Word vba) there is an option whereby you can save the form data as a text file, which would be a much simpler method of gathering data. If not, you'll have to loop through the tables as suggested above.

[Belch]

Thanks for the tips, I'll have a play around both ways and see how it goes.