Solved

Automation Error

Posted on 2003-10-22
19
303 Views
Last Modified: 2012-06-21
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
Comment
Question by:joannagirl
  • 6
  • 5
  • 4
  • +2
19 Comments
 
LVL 11

Expert Comment

by:joekendall
ID: 9599564
Have you done a repair/compact on the database? I would start there first.

Joe
0
 

Author Comment

by:joannagirl
ID: 9599604
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
 
LVL 11

Expert Comment

by:joekendall
ID: 9599652
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
 

Author Comment

by:joannagirl
ID: 9599831
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
 
LVL 11

Expert Comment

by:joekendall
ID: 9599886
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
 
LVL 9

Expert Comment

by:rgcole
ID: 9599906
Try rebooting if you run out of other remedies.
Robert
0
 
LVL 11

Expert Comment

by:joekendall
ID: 9599909
Also what version of Word were you using? Has the version changed from 2000 to 2002?

Joe
0
 
LVL 9

Expert Comment

by:rgcole
ID: 9599915
Or, rebooting the network.  I had some Access file lock issues that were remedied this way (twice).

Robert
0
 
LVL 2

Expert Comment

by:jonathonberg
ID: 9600003
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:joannagirl
ID: 9600009
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
 
LVL 11

Expert Comment

by:joekendall
ID: 9600058
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
 

Author Comment

by:joannagirl
ID: 9600261
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
 

Author Comment

by:joannagirl
ID: 9600792
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
 
LVL 2

Accepted Solution

by:
jonathonberg earned 500 total points
ID: 9601045
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
 
LVL 2

Expert Comment

by:jonathonberg
ID: 9601086
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
 
LVL 39

Expert Comment

by:stevbe
ID: 9990898
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
 
LVL 2

Expert Comment

by:jonathonberg
ID: 9993635
I put in the time on this one - And I think the fix was good - But we don't know do we?
0
 

Author Comment

by:joannagirl
ID: 10015750
The fix was good and it worked for me. Thank you for your assistance.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now