Greetings,
I am a novice when it comes to programming, and after doing some extensive research, it seems that programming is the only way to get Microsoft Access 2010 to grab from a Microsoft Word (2010) form.
Currently a Word form is filled out when some kind of building maintenance needs done and that Word form is sent (via email attached) to our secretary who has been manually entering the data into an Excel spreadsheet. I thought there has to be an easier way to do this so I told her I would take a stab at it. By the way these forms are continually being processed, it is not just a one time thing so exporting/importing the data is not really an effiecient option. I created an Access table to store the data and I actually found some code online from Windows 2000, but it is not working and I am not really sure why.
I am using Office 2010
Windows XP
Can anyone help direct me maybe as to why this code does not work and maybe to a better solution. Any help would be greatly appreciated.
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:\Documents and Settings\My Documents\WorkOrders\" & _
InputBox("Enter the name of the Word Order " & _
"you want to import:", "Import Work Order")
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"WorkOrders.mdb;"
rst.Open "tblWorkOrders", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
!Requestor = doc.FormFields("fldName").Result
!Title = doc.FormFields("fldTitle").Result
!Email = doc.FormFields("fldEmail Address").Result
!Phone = doc.FormFields("fldPhone").Result
!Location = doc.FormFields("fldPSW Location").Result
!DateSubmitted = doc.FormFields("fldDate Submitted").Result
!BuildingOrGrounds = doc.FormFields("fldBuilding or Grounds:").Results
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "WOrk Order 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.Application")
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