please help me out!!! import word into access 2000

S

snowiii

Jeanette:

I took your code and modified it (see below)...It keeps coming back with the
message "You must select a valid word document. No data imported." I made
sure there are two word docuemnts in the target directory...What could be
causing this to occur?

Thanks!
Snowiii

Private Sub GetWordData_Click()
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 strSourceFolder As String
Dim strFileName As String

On Error GoTo ErrorHandling

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

strSourceFolder = strPath & "\"

strFileName = Dir(strSourceFolder)

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

Do While Left$(strFileName, 2) <> "xx"
strFileName = Dir(strSourceFolder)
If Left$(strFileName, 2) = "xx" Then
'don't process
Else
'Debug.Print strFileName


Set doc = appWord.Documents.Open(strFileName)

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

With rst
.AddNew
!Requestor = doc.FormFields("Requestor").Result
!CvgType = doc.FormFields("CvgType").Result
!CAT = doc.FormFields("CAT").Result
!RequestDate = doc.FormFields("RequestDate").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

.Update
.Close
End With
doc.Close

Name strSourceFolder & strFileName As strSourceFolder & "xx" & strFileName
End If
Loop


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
 
J

Jeanette Cunningham

Here is amended and tested code. It works for me.


Private Sub ExportADODB()
'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 = "C:\...." & "\"

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Jeanette\My Documents\export
sample.mdb"

rst.Open "tblImportWordForms", cnn, _
adOpenKeyset, adLockOptimistic

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

With rst
.AddNew
!FirstName = doc.FormFields("fldFirstName").Result
!LastName = doc.FormFields("fldLastName").Result
!Company = doc.FormFields("fldCompany").Result
!Address = doc.FormFields("fldAddress").Result
!City = doc.FormFields("fldCity").Result
!State = doc.FormFields("fldState").Result
!ZIP = doc.FormFields("fldZIP").Result
!Phone = doc.FormFields("fldPhone").Result
!SocialSecurity = doc.FormFields("fldSocialSecurity").Result
!Gender = doc.FormFields("fldGender").Result
!BirthDate = doc.FormFields("fldBirthDate").Result
!AdditionalCoverage = _
doc.FormFields("fldAdditional").Result
.Update
End With
doc.Close
Set doc = Nothing
Name strPath & strFileName As strPath & "xx" & strFileName
End If
Loop

rst.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.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


Jeanette Cunningham
 
S

snowiii

Jeanette:

Thanks for the update...Unfortuantely I now have another error
appearing...The message is: "52: Bad file name or number"...the files I am
looking to import are named "Auto1.doc" and "Property1.doc"...I don't know if
their naming convention is the issue or if the code I modified has a mistake
in it...Could you assist?

Thanks for all you help to-date...It is much appreciated!

:-}

Snowiii

HERE IS THE MODIFIED CODE:

ub GetWordData()
'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.mdb;"


rst.Open "MainTbl", cnn, _
adOpenKeyset, adLockOptimistic

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

With rst

.AddNew
!RequestDate = doc.FormFields("RequestDate").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
.Update
End With
doc.Close
Set doc = Nothing
Name strPath & strFileName As strPath & "xx" & strFileName
End If
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
 
J

Jeanette Cunningham

Tom,
Please try the import with a couple of test word docs that you create
yourself.
Set up a new word form and create 2 or 3 files using the form.
Try to import those files using the code below.
To make the test quicker, only create 2 or 3 fields and comment out the
lines in the code that refer to fields that are not on the test docs.
If that works OK, then we can be pretty sure that there is something about
the files that won't import and you can open them and have a look to see
what might be wrong.
You can also step through the code to see the line on which it errors out.

Jeanette Cunningham
 
S

snowiii

Jeanette:

I figured out that problem...Now when I import files, it doesn't immediately
import all files in the target directory...So I did a test...Put 4 files in
the directory to import from...It pulled in two...I removed them and tried
importing again...this time it imported 1 of the two. I removed that one and
tried again...It then imported the last one...Any idea why it did not import
all 4 the first time?

Thanks!

Tom
 

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

Please help me choose a product for the task 2
Word doc keeps changing to ALL CAPS 0
Clearing Fields in a Word Form 5
Repeated Requests for GSuite password when first Accessing Outlook 365 after system start 0
MS365 WORD: How to Create Macro Button to CLEAR ALL fields in fill-in form 1
Need Help with Macro Security 1
Cannot open any folders (pinned or recent) from Word 365 Open Dialogue/Backstage 0
Working on birthday "book" for my wife and having layout/editing trouble 0

Top