[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Automation Error

I have a MS Word template that a user fills out and saves to a specific drive and directory. I've written code in MS Access that looks @ this directory and takes all of the .doc files and populates the MS Access database. Everything was working fine. This morning I tried running a test from beginning (creating the MS Word doc) to end (Importing the docs.) and I am now receiving an Automation error: The object invoked has disconnected from its clients. I've also had someone else try on their PC and they too are receiving the same error. The import still works on the other persons PC, but not on mine. Has anyone experienced this problem before and where do I start. Any help is greatly appreciated.
0
joannagirl
Asked:
joannagirl
  • 6
  • 5
  • 4
  • +2
1 Solution
 
joekendallCommented:
Have you done a repair/compact on the database? I would start there first.

Joe
0
 
joannagirlAuthor Commented:
I just performed the repair/compact on the database. I tried running my test and I am still receiving the automation error and the import did not working on my machine. Thank you. Is there anything else I can do?
0
 
joekendallCommented:
Have you had any new software installed or any upgrades recently?

What type of binding are you using to automate Word?

Have you checked to see if you are missing any references?

Joe
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
joannagirlAuthor Commented:
I am verifying if any new software installs or upgrades were performed. I do not believe so, but I am verifying.

I do not believe any references are missing.

I am sending you my code if that would be of any assistance in assisting me.

Many thanks for your help and support.

Public 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
Dim strpath As String
Dim strfile As String
Dim strfilename As String

strpath = "C:\TestFolder\TestCases\"
strfile = Dir(strpath & "*.doc")
strfilename = strpath & strfile

On Error GoTo ErrorHandling


cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Testfolder\Customfeed.mdb;"
rst.Open "tblfeedbck", cnn, _
        adOpenKeyset, adLockOptimistic
       
' Outer WHILE
strfile = Dir(strfilename)
Do While strfile <> ""
     If strfile <> "." And strfile <> ".." Then
       strDocName = strpath & strfile
Set appWord = GetObject(, "Word.Application")
blnQuitWord = True
Set doc = appWord.Documents.Open(strDocName)
If ActiveDocument.ProtectionType <> wdNoProtection Then
   ActiveDocument.Unprotect
End If
 
 With rst
     .AddNew
       
    !FRN = doc.FormFields("txtFRN").Result
               
    If (IsNull(doc.FormFields("dteSessionDate").Result)) Then
         !SessionDate = ""
    Else
         !SessionDate = doc.FormFields("dteSessionDate").Result
    End If
   
    If (IsNull(doc.FormFields("Dropdown1").Result)) Then
         !BranchName = ""
    Else
         !BranchName = doc.FormFields("Dropdown1").Result
    End If
   
    If (IsNull(doc.FormFields("Dropdown2").Result)) Then
         !ITName = ""
    Else
         !ITName = doc.FormFields("Dropdown2").Result
    End If
   
    If (IsNull(doc.FormFields("txtITLeadName").Result)) Then
         !ITLeadName = ""
    Else
         !ITLeadName = doc.FormFields("txtITLeadName").Result
    End If
   
    If (IsNull(doc.FormFields("txtITLExt").Result)) Then
         !ITLExt = ""
    Else
         !ITLExt = doc.FormFields("txtITLExt").Result
    End If
   
    If (IsNull(doc.FormFields("txtFSFName").Result)) Then
         !FSFName = ""
    Else
         !FSFName = doc.FormFields("txtFSFName").Result
    End If
   
    If (IsNull(doc.FormFields("txtFSFExt").Result)) Then
         !FSFExt = ""
    Else
         !FSFExt = doc.FormFields("txtFSFExt").Result
    End If
   
    If (IsNull(doc.FormFields("intNoAttendees").Result)) Then
         !NoAttendees = "00"
    Else
         !NoAttendees = doc.FormFields("intNoAttendees").Result
    End If
   
    If (IsNull(doc.FormFields("frmcombo").Result)) Then
         !PGSecNo = ""
    Else
         !PGSecNo = doc.FormFields("frmcombo").Result
    End If
   
    If (IsNull(doc.FormFields("Dropdown4").Result)) Then
         !FBSource = ""
    Else
         !FBSource = doc.FormFields("Dropdown4").Result
    End If
   
    If (IsNull(doc.FormFields("Dropdown5").Result)) Then
         !FBCategory = ""
    Else
         !FBCategory = doc.FormFields("Dropdown5").Result
    End If
   
    If (IsNull(doc.FormFields("memFBExpSummary").Result)) Then
         !FBExpSummary = ""
    Else
         !FBExpSummary = doc.FormFields("memFBExpSummary").Result
    End If
   
    If (IsNull(doc.FormFields("memFBExpDetail").Result)) Then
         !FBExpDetail = ""
    Else
         !FBExpDetail = doc.FormFields("memFBExpDetail").Result
    End If
   
    If (IsNull(doc.FormFields("memProposedAct").Result)) Then
         !ProposedAction = ""
    Else
         !ProposedAction = doc.FormFields("memProposedAct").Result
    End If
       
    If (IsNull(doc.FormFields("intNoSimilarExp").Result)) Then
         !NoSimilarExp = ""
    Else
         !NoSimilarExp = doc.FormFields("intNoSimilarExp").Result
    End If
   
    If (IsNull(doc.FormFields("Dropdown6").Result)) Then
         !FBPOC = ""
    Else
         !FBPOC = doc.FormFields("Dropdown6").Result
    End If
   
    If (IsNull(doc.FormFields("txtFBPOC").Result)) Then
         !FBPOC = ""
    Else
         !FBPOC = doc.FormFields("txtFBPOC").Result
    End If
             
    If (IsNull(doc.FormFields("txtPOCExt").Result)) Then
         !POCExt = ""
    Else
         !POCExt = doc.FormFields("txtPOCExt").Result
    End If
                               
    If (IsNull(doc.FormFields("memNoAct").Result)) Then
         !AnticipatedImpactNoAction = ""
    Else
         !AnticipatedImpactNoAction = doc.FormFields("memNoAct").Result
    End If
       
    If (IsNull(doc.FormFields("memIsAct").Result)) Then
         !AnticipatedImpactIsAction = ""
    Else
         !AnticipatedImpactIsAction = doc.FormFields("memIsAct").Result
    End If
           
  End With
 
End If
 
 doc.Close
 
strfile = Dir
Loop        ' DO WHILE strFileName<>""

'If blnQuitWord Then appWord.Quit

cnn.Close

MsgBox "Document(s)Imported!"

If Not IsNull(doc) Then doc.Close

Cleanup:

If blnQuitWord Then appWord.Quit

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
0
 
joekendallCommented:
When you are in your code module and you go to the Tools menu and References, do you see any references that have MISSING by them?

Joe
0
 
rgcoleCommented:
Try rebooting if you run out of other remedies.
Robert
0
 
joekendallCommented:
Also what version of Word were you using? Has the version changed from 2000 to 2002?

Joe
0
 
rgcoleCommented:
Or, rebooting the network.  I had some Access file lock issues that were remedied this way (twice).

Robert
0
 
jonathonbergCommented:
You need to tell us which version of Word and Access are being used.

If the issue is reference related, try creating a new EMPTY mdb file and then usinf File/Get External Data to import EVERYTHING from your old mdb.  You will need to set the references again and also the startup options, but otherwise this is a quick option.  It will clean the database better than a compress/repair.

This will also solve the problem if it was caused by a user with a more advanced version of Office running the app and therefor upgrading the references in it.

Finally, I would consider downgrading the ADODB code to old fashioned DAO, because its simpler and less flaky.

Jonathon.
0
 
joannagirlAuthor Commented:
I did as you said, and I did not see any that has MISSING by them. The ones that I do have checked are as follows:
Visual Basics for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
and
Microsoft Word 9.0 Object Library.

No one has called me back to verify an install or upgrade as of yet.
0
 
joekendallCommented:
Why not give what Jonathon suggested a try? Create a new database and import all of the objects into it. Then give it a whirl and see if it works.

Joe
0
 
joannagirlAuthor Commented:
I have MS Word2000, MS Access2000 running under Windows2000.

I will take Jonathan suggestion and will let you know. Again, Thank you for all of the assistance.
0
 
joannagirlAuthor Commented:
I recreated the database, and re-set my references and I am still getting the same error message, and no data is being imported.
I heard from the LAN staff, and no new installs or upgrades were performed.
If you have any other suggestions, please let me know.

Thank you for your assistance.
0
 
jonathonbergCommented:
Ok -

Try replacing

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

And, at the end (just before the end of the outer loop) close Word again using

appword.quit

This will run slower, but it may help to identify the problem.  Also, have you told us at what point the code fails?

Jonathon.
0
 
jonathonbergCommented:
Thinking about it, this HAS to be talking about Word (the Automation client).  Assuming you have rebooted, it sounds like something is interfering with the loop.

A msgbox on-screen each time round the loop would confirm that it is going round at least once.

Also, setting the object variable (appWord) to Nothing at the end of each loop might help

Set appWord = Nothing



I'm going home now (it's 7:30 pm where I am)

I'll try logging on at home and see how you got on.


Jonathon.
0
 
stevbeCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: jonathonberg {http:#9601045}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0
 
jonathonbergCommented:
I put in the time on this one - And I think the fix was good - But we don't know do we?
0
 
joannagirlAuthor Commented:
The fix was good and it worked for me. Thank you for your assistance.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now