Importing File Name

S

snowiii

I am trying to input form fields from a word doc into access...I alos want to
add the file name as a field into the table with the data fields...I am
running up against a brick wall on this and am a rookie at VB....I have the
code shown below...Any help would be appreciated.

Snowiii

Private Sub GetWordData_Click()
'You need to add the path to the docs you want to process - this is hard
'coded where you see strPath = "C:\....."
'
'The process imports the first word doc, renames the doc with xx in front of
'the doc name and then does the next doc.
'You will end up with every word doc renamed with xx in front of the file
'name.
'This code assumes that only word docs are stored in the folder.


Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim blnQuitWord As Boolean
Dim strPath As String
Dim strFileName As String

On Error GoTo ErrorHandling


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

'hard code the path and folder where the word forms are stored
strPath = "x:\Tom Snow\Temp" & "\"

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= X:\Tom Snow\Coverage Verification Project\db folder\" & _
"Cart v2.mdb;"


rst.Open "InputTbl", 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
!RequestDate = doc.FormFields("RequestDate").Result
!Requestor = doc.FormFields("Requestor").Result
!CvgType = doc.FormFields("CvgType").Result
!Handler = doc.FormFields("Handler").Result
!HandlerPhone = doc.FormFields("HandlerPhone").Result
!Office = doc.FormFields("Office").Result
!Policy = doc.FormFields("Policy").Result
!EventNo = doc.FormFields("EventNo").Result
!Insured = doc.FormFields("Insured").Result
!DOL = doc.FormFields("DOL").Result
!VehicleYear = doc.FormFields("VehicleYear").Result
!VehicleMake = doc.FormFields("VehicleMake").Result
!VehicleModel = doc.FormFields("VehicleModel").Result
!VIN = doc.FormFields("VIN").Result
!ApproximateReserve = doc.FormFields("ApproximateReserve").Result
!Address = doc.FormFields("Address").Result
!LossLoc = doc.FormFields("LossLoc").Result
!DescriptionOfLoss = doc.FormFields("DescriptionOfLoss").Result
!UnverifiedStatus = doc.FormFields("UnverifiedStatus").Result
!InsuredPhone = doc.FormFields("InsuredPhone").Result
!InsuredState = doc.FormFields("InsuredState").Result
!InsuredZip = doc.FormFields("InsuredZip").Result
!Schedule = doc.FormFields("Schedule").Result
!BIX = doc.FormFields("Bix").Result
!IneligibleStatus = doc.FormFields("IneligibleStatus").Result
!CancelledStatus = doc.FormFields("CancelledStatus").Result
!PayLapseStatus = doc.FormFields("PayLapseStatus").Result
!MicrofilmStatus = doc.FormFields("MicrofilmStatus").Result
!VNOP = doc.FormFields("VNOP").Result
!VehiclePurchasedDate = doc.FormFields("VehiclePurchasedDate").Result
!CustomerNotifyDate = doc.FormFields("CustomerNotifyDate").Result
!CopyOfPolicy = doc.FormFields("CopyofPolicy").Result
!CopyOfApplication = doc.FormFields("CopyofApplication").Result
!UnderwritingFile = doc.FormFields("UnderwritingFile").Result
!Comments = doc.FormFields("Comments").Result
!CopyAppComments = doc.FormFields("CopyAppComments").Result
!WritingCo = doc.FormFields("WritingCo").Result
!strFileName = doc.FormFields("FileName").Result <- Here is my problem!

.Update
End With
doc.Close
Set doc = Nothing
Name strPath & strFileName As strPath & "xx" & strFileName
End If
strFileName = Dir()
Loop

rst.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Inquiry 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
 
B

bart.nissen

I am trying to input form fields from a word doc into access...I alos wantto
add the file name as a field into the table with the data fields...I am
running up against a brick wall on this and am a rookie at VB....I have the
code shown below...Any help would be appreciated.

Snowiii

Private Sub GetWordData_Click()
'You need to add the path to the docs you want to process - this is hard
'coded where you see strPath = "C:\....."
'
'The process imports the first word doc, renames the doc with xx in front of
'the doc name and then does the next doc.
'You will end up with every word doc renamed with xx in front of the file
'name.
'This code assumes that only word docs are stored in the folder.

Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim blnQuitWord As Boolean
Dim strPath As String
Dim strFileName As String

On Error GoTo ErrorHandling

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

'hard code the path and folder where the word forms are stored
strPath = "x:\Tom Snow\Temp" & "\"

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= X:\Tom Snow\Coverage Verification Project\db folder\" & _
    "Cart v2.mdb;"

rst.Open "InputTbl", 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
    !RequestDate = doc.FormFields("RequestDate").Result
    !Requestor = doc.FormFields("Requestor").Result
    !CvgType = doc.FormFields("CvgType").Result
    !Handler = doc.FormFields("Handler").Result
    !HandlerPhone = doc.FormFields("HandlerPhone").Result
    !Office = doc.FormFields("Office").Result
    !Policy = doc.FormFields("Policy").Result
    !EventNo = doc.FormFields("EventNo").Result
    !Insured = doc.FormFields("Insured").Result
    !DOL = doc.FormFields("DOL").Result
    !VehicleYear = doc.FormFields("VehicleYear").Result
    !VehicleMake = doc.FormFields("VehicleMake").Result
    !VehicleModel = doc.FormFields("VehicleModel").Result
    !VIN = doc.FormFields("VIN").Result
    !ApproximateReserve = doc.FormFields("ApproximateReserve").Result
    !Address = doc.FormFields("Address").Result
    !LossLoc = doc.FormFields("LossLoc").Result
    !DescriptionOfLoss = doc.FormFields("DescriptionOfLoss").Result
    !UnverifiedStatus = doc.FormFields("UnverifiedStatus").Result
    !InsuredPhone = doc.FormFields("InsuredPhone").Result
    !InsuredState = doc.FormFields("InsuredState").Result
    !InsuredZip = doc.FormFields("InsuredZip").Result
    !Schedule = doc.FormFields("Schedule").Result
    !BIX = doc.FormFields("Bix").Result
    !IneligibleStatus = doc.FormFields("IneligibleStatus").Result
    !CancelledStatus = doc.FormFields("CancelledStatus").Result
    !PayLapseStatus = doc.FormFields("PayLapseStatus").Result
    !MicrofilmStatus = doc.FormFields("MicrofilmStatus").Result
    !VNOP = doc.FormFields("VNOP").Result
    !VehiclePurchasedDate = doc.FormFields("VehiclePurchasedDate").Result
    !CustomerNotifyDate = doc.FormFields("CustomerNotifyDate").Result
    !CopyOfPolicy = doc.FormFields("CopyofPolicy").Result
    !CopyOfApplication = doc.FormFields("CopyofApplication").Result
    !UnderwritingFile = doc.FormFields("UnderwritingFile").Result
    !Comments = doc.FormFields("Comments").Result
    !CopyAppComments = doc.FormFields("CopyAppComments").Result
    !WritingCo = doc.FormFields("WritingCo").Result
    !strFileName = doc.FormFields("FileName").Result <- Here is my problem!

    .Update
End With
doc.Close
Set doc = Nothing
Name strPath & strFileName As strPath & "xx" & strFileName
End If
strFileName = Dir()
Loop

rst.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Inquiry 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
Click to expand...
Change it to:
!strFileName = strPath & strFileName
Your code assumes that you have a bookmark on the word document called
"FileName".
 
S

snowiii

I tried this an nogo...I need to import the file name into a named field in
the table called "FileName" which is a text field. I tried the below and
received an error "Compile error. Expected array"...

Here is the altered code....

!strFileName = strPath & strFileName(FileName).Result
 
H

hillbillykeith

snowiii said:
I am trying to input form fields from a word doc into access...I alos want
to
add the file name as a field into the table with the data fields...I am
running up against a brick wall on this and am a rookie at VB....I have
the
code shown below...Any help would be appreciated.

Snowiii

Private Sub GetWordData_Click()
'You need to add the path to the docs you want to process - this is hard
'coded where you see strPath = "C:\....."
'
'The process imports the first word doc, renames the doc with xx in front
of
'the doc name and then does the next doc.
'You will end up with every word doc renamed with xx in front of the file
'name.
'This code assumes that only word docs are stored in the folder.


Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim blnQuitWord As Boolean
Dim strPath As String
Dim strFileName As String

On Error GoTo ErrorHandling


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

'hard code the path and folder where the word forms are stored
strPath = "x:\Tom Snow\Temp" & "\"

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= X:\Tom Snow\Coverage Verification Project\db folder\" & _
"Cart v2.mdb;"


rst.Open "InputTbl", 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
!RequestDate = doc.FormFields("RequestDate").Result
!Requestor = doc.FormFields("Requestor").Result
!CvgType = doc.FormFields("CvgType").Result
!Handler = doc.FormFields("Handler").Result
!HandlerPhone = doc.FormFields("HandlerPhone").Result
!Office = doc.FormFields("Office").Result
!Policy = doc.FormFields("Policy").Result
!EventNo = doc.FormFields("EventNo").Result
!Insured = doc.FormFields("Insured").Result
!DOL = doc.FormFields("DOL").Result
!VehicleYear = doc.FormFields("VehicleYear").Result
!VehicleMake = doc.FormFields("VehicleMake").Result
!VehicleModel = doc.FormFields("VehicleModel").Result
!VIN = doc.FormFields("VIN").Result
!ApproximateReserve = doc.FormFields("ApproximateReserve").Result
!Address = doc.FormFields("Address").Result
!LossLoc = doc.FormFields("LossLoc").Result
!DescriptionOfLoss = doc.FormFields("DescriptionOfLoss").Result
!UnverifiedStatus = doc.FormFields("UnverifiedStatus").Result
!InsuredPhone = doc.FormFields("InsuredPhone").Result
!InsuredState = doc.FormFields("InsuredState").Result
!InsuredZip = doc.FormFields("InsuredZip").Result
!Schedule = doc.FormFields("Schedule").Result
!BIX = doc.FormFields("Bix").Result
!IneligibleStatus = doc.FormFields("IneligibleStatus").Result
!CancelledStatus = doc.FormFields("CancelledStatus").Result
!PayLapseStatus = doc.FormFields("PayLapseStatus").Result
!MicrofilmStatus = doc.FormFields("MicrofilmStatus").Result
!VNOP = doc.FormFields("VNOP").Result
!VehiclePurchasedDate = doc.FormFields("VehiclePurchasedDate").Result
!CustomerNotifyDate = doc.FormFields("CustomerNotifyDate").Result
!CopyOfPolicy = doc.FormFields("CopyofPolicy").Result
!CopyOfApplication = doc.FormFields("CopyofApplication").Result
!UnderwritingFile = doc.FormFields("UnderwritingFile").Result
!Comments = doc.FormFields("Comments").Result
!CopyAppComments = doc.FormFields("CopyAppComments").Result
!WritingCo = doc.FormFields("WritingCo").Result
!strFileName = doc.FormFields("FileName").Result <- Here is my problem!

.Update
End With
doc.Close
Set doc = Nothing
Name strPath & strFileName As strPath & "xx" & strFileName
End If
strFileName = Dir()
Loop

rst.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Inquiry 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
Click to expand...
 
H

hillbillykeith

snowiii said:
I am trying to input form fields from a word doc into access...I alos want
to
add the file name as a field into the table with the data fields...I am
running up against a brick wall on this and am a rookie at VB....I have
the
code shown below...Any help would be appreciated.

Snowiii

Private Sub GetWordData_Click()
'You need to add the path to the docs you want to process - this is hard
'coded where you see strPath = "C:\....."
'
'The process imports the first word doc, renames the doc with xx in front
of
'the doc name and then does the next doc.
'You will end up with every word doc renamed with xx in front of the file
'name.
'This code assumes that only word docs are stored in the folder.


Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim blnQuitWord As Boolean
Dim strPath As String
Dim strFileName As String

On Error GoTo ErrorHandling


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

'hard code the path and folder where the word forms are stored
strPath = "x:\Tom Snow\Temp" & "\"

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= X:\Tom Snow\Coverage Verification Project\db folder\" & _
"Cart v2.mdb;"


rst.Open "InputTbl", 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
!RequestDate = doc.FormFields("RequestDate").Result
!Requestor = doc.FormFields("Requestor").Result
!CvgType = doc.FormFields("CvgType").Result
!Handler = doc.FormFields("Handler").Result
!HandlerPhone = doc.FormFields("HandlerPhone").Result
!Office = doc.FormFields("Office").Result
!Policy = doc.FormFields("Policy").Result
!EventNo = doc.FormFields("EventNo").Result
!Insured = doc.FormFields("Insured").Result
!DOL = doc.FormFields("DOL").Result
!VehicleYear = doc.FormFields("VehicleYear").Result
!VehicleMake = doc.FormFields("VehicleMake").Result
!VehicleModel = doc.FormFields("VehicleModel").Result
!VIN = doc.FormFields("VIN").Result
!ApproximateReserve = doc.FormFields("ApproximateReserve").Result
!Address = doc.FormFields("Address").Result
!LossLoc = doc.FormFields("LossLoc").Result
!DescriptionOfLoss = doc.FormFields("DescriptionOfLoss").Result
!UnverifiedStatus = doc.FormFields("UnverifiedStatus").Result
!InsuredPhone = doc.FormFields("InsuredPhone").Result
!InsuredState = doc.FormFields("InsuredState").Result
!InsuredZip = doc.FormFields("InsuredZip").Result
!Schedule = doc.FormFields("Schedule").Result
!BIX = doc.FormFields("Bix").Result
!IneligibleStatus = doc.FormFields("IneligibleStatus").Result
!CancelledStatus = doc.FormFields("CancelledStatus").Result
!PayLapseStatus = doc.FormFields("PayLapseStatus").Result
!MicrofilmStatus = doc.FormFields("MicrofilmStatus").Result
!VNOP = doc.FormFields("VNOP").Result
!VehiclePurchasedDate = doc.FormFields("VehiclePurchasedDate").Result
!CustomerNotifyDate = doc.FormFields("CustomerNotifyDate").Result
!CopyOfPolicy = doc.FormFields("CopyofPolicy").Result
!CopyOfApplication = doc.FormFields("CopyofApplication").Result
!UnderwritingFile = doc.FormFields("UnderwritingFile").Result
!Comments = doc.FormFields("Comments").Result
!CopyAppComments = doc.FormFields("CopyAppComments").Result
!WritingCo = doc.FormFields("WritingCo").Result
!strFileName = doc.FormFields("FileName").Result <- Here is my problem!

.Update
End With
doc.Close
Set doc = Nothing
Name strPath & strFileName As strPath & "xx" & strFileName
End If
strFileName = Dir()
Loop

rst.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Inquiry 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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Word Doc Import Loop 2
Module For Importing Word Forms 4
Importing Word Form into Access.... part2 3
Importing Word form data to Access 3
Importing from Word Form 0
Importing Word Form data into Access 1

Top