fishrich
asked on
Access 2007 Module Code to Import Word Doc Fields
First off I just want to start off by thanking all of the experts for sharing their knowledge!!! You guys really make this the best page to come for research&. Now on to my issue..
I would consider myself an experienced novice user with Access and here is my problematic scenario:
What I am trying to do is have my users fill out a word document form, and then using a module import the fields into multiple tables in my DB.
I created a Word Form 2003 type, and created the fields in it with matching names the multiple table fields names.
I have built a db in MS ACCESS 2007 and the structure is as such: TABLE1, TABLE2, TABLE3& With fields as such: TABLE1 FIELDA FIELDB FIELDC; TABLE2 FIELD1 FIELD2 FIELD3, TABLE3 FIELD* FIELD** FIELD***
I need assistance with the proper code to create a module to import the info from the Word Doc fields into the multiple Tables in the DB.
I have tried to use something along these lines but obviously the code is old and the syntax is for a totally different version of the Jet engine. I think this would be a good starting ground, but I dont know how to code and so I ask the experts for help!!!
http://msdn2.microsoft.com/en-us/library/aa155434(office.10).aspx
Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
On Error GoTo ErrorHandling
strDocName = "C:\Contracts\" & _
InputBox("Enter the name of the Word contract " & _
"you want to import:", "Import Contract")
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(str DocName)
cnn.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
!FirstName = doc.FormFields("fldFirstNa me").Resul t
!LastName = doc.FormFields("fldLastNam e").Result
!Company = doc.FormFields("fldCompany ").Result
!Address = doc.FormFields("fldAddress ").Result
!City = doc.FormFields("fldCity"). Result
!State = doc.FormFields("fldState") .Result
!ZIP = doc.FormFields("fldZIP1"). Result & _
"-" & doc.FormFields("fldZIP2"). Result
!Phone = doc.FormFields("fldPhone") .Result
!SocialSecurity = doc.FormFields("fldSocialS ecurity"). Result
!Gender = doc.FormFields("fldGender" ).Result
!BirthDate = doc.FormFields("fldBirthDa te").Resul t
!AdditionalCoverage = _
doc.FormFields("fldAdditio nal").Resu lt
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"
Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Applica tion")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub
Thank you for you help!
I would consider myself an experienced novice user with Access and here is my problematic scenario:
What I am trying to do is have my users fill out a word document form, and then using a module import the fields into multiple tables in my DB.
I created a Word Form 2003 type, and created the fields in it with matching names the multiple table fields names.
I have built a db in MS ACCESS 2007 and the structure is as such: TABLE1, TABLE2, TABLE3& With fields as such: TABLE1 FIELDA FIELDB FIELDC; TABLE2 FIELD1 FIELD2 FIELD3, TABLE3 FIELD* FIELD** FIELD***
I need assistance with the proper code to create a module to import the info from the Word Doc fields into the multiple Tables in the DB.
I have tried to use something along these lines but obviously the code is old and the syntax is for a totally different version of the Jet engine. I think this would be a good starting ground, but I dont know how to code and so I ask the experts for help!!!
http://msdn2.microsoft.com/en-us/library/aa155434(office.10).aspx
Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
On Error GoTo ErrorHandling
strDocName = "C:\Contracts\" & _
InputBox("Enter the name of the Word contract " & _
"you want to import:", "Import Contract")
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(str
cnn.Open "Provider=Microsoft.Jet.OL
"Data Source=C:\My Documents\" & _
"Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
!FirstName = doc.FormFields("fldFirstNa
!LastName = doc.FormFields("fldLastNam
!Company = doc.FormFields("fldCompany
!Address = doc.FormFields("fldAddress
!City = doc.FormFields("fldCity").
!State = doc.FormFields("fldState")
!ZIP = doc.FormFields("fldZIP1").
"-" & doc.FormFields("fldZIP2").
!Phone = doc.FormFields("fldPhone")
!SocialSecurity = doc.FormFields("fldSocialS
!Gender = doc.FormFields("fldGender"
!BirthDate = doc.FormFields("fldBirthDa
!AdditionalCoverage = _
doc.FormFields("fldAdditio
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"
Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Applica
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub
Thank you for you help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
ASKER