Help with code modification
 

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/libr...ffice.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

Re: Help with code modification
 

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.