0

I'll start off saying I am not a programmer. I am making a database that imports data from Word forms. It requires code and I found some that works almost perfectly for me at http://msdn.microsoft.com/en-us/library/aa155434(office.10).aspx
It does exactly what I need. The only thing that would make this code better would be to use relative references to the database and imported files.

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(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=C:\My Documents\" & _
	"Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
	adOpenKeyset, adLockOptimistic

In a previous database, I was able to use CurrentProject.Path to allow for relative paths, but I can't figure out how to get that to work with this code.

Can anyone please help me by letting me know how to set up a relative path for the referencing. I am planning on storing the database and files to be imported in the same folder.

Thank you

1
Contributor
1
Reply
2
Views
9 Years
Discussion Span
Last Post by HI2Japan
0

So again I'm not a programmer, but here is the finished product after searching through a lot of websites. It works so if anyone is ever trying to take Word form data and import it via Access, use this code. It will search all of the .doc files within the directory containing the DB and after it imports them, rename the file to have an "xx" in the front. It will not import those files again.

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 strFileName As String
Dim blnQuitWord As Boolean
Dim strPath As String


On Error GoTo ErrorHandling

Set appWord = GetObject(, "Word.Application")

strPath = CurrentProject.Path + "\"

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" + CurrentProject.Path + "\WorkstationRefresh.mdb;"
rst.Open "tblData", cnn, _
    adOpenKeyset, adLockOptimistic

strFileName = Dir(strPath & "*.doc")

Do While strFileName <> vbNullString

Debug.Print strFileName
If Left$(strFileName, 2) <> "xx" Then
'don't process
Debug.Print strPath & strFileName
Set doc = appWord.Documents.Open(strPath & strFileName)

With rst
.AddNew
    !LName = doc.FormFields("LName").Result  'put in the rest of your fields in the same way
.Update
End With
doc.Close
Set doc = Nothing
Name strPath & strFileName As strPath & "xx" & strFileName
End If
strFileName = Dir()
Loop

If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Workstation data 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

This would be much more efficient if a loop was set up to read through the word form fields, but I couldn't figure out how to get that to work.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.