• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4770
  • Last Modified:

Automation error The object invoked has disconnected from its clients.

In an Excel VBA project I get the following error message:

     Run-time error '2147417848(800 10 108)':

     Automation error  
     The object invoked has disconnected from its clients.

On the following code located just before the "End Sub" line:
...
     Worksheets("Data").Range("exitNames").value = Application.Transpose(exitString)
End Sub

Curiously the application generates this error when I've made seeming minor changes to various parts of the program.  In the most recent instance the exitString array contains 3619 string values recording states I use for debugging.  I changed what gets writtin to the array values but didn't do anything weird.

The array itself looks fine all but 60 or so values only have "" in them, just as I would expect.  

Have I pushed up against some memory limit?

Three other lines of code:
   Worksheets("Data").Range("trigger").value = Application.Transpose(trigger)
   Worksheets("Data").Range("openTradeNames").value = Application.Transpose(openTradeString)  
   Worksheets("Data").Range("openPosition").value = Application.Transpose(openPosition)  

located just prior to the one which generates the error message work fine.  The middle one is the same size and type of array as the one with which I have problems.

Any ideas?

A
0
AlphaSquared
Asked:
AlphaSquared
3 Solutions
 
AlphaSquaredAuthor Commented:
I abreviated the strings I capture in the array "exitString()" and ran the code again.  It ran fine writing  everything to the Excel worksheet.  

BUT wierd things started to happen:

1. The hard drive is whiring like crazy
2. The performance monitor shows CPU usage btween 75% and 99%
3. If I click on a cell in Excel that has a string from the array in question CPU performance pushes the upper limits and
     the edit bar in Excel keeps bliking like crazy.
4. The Excel.Exe process appears to command 49% to 50% of CPU availability

Oh, an all this has gone on for the past 5 minutes.

Huh???
0
 
PreeceCommented:
Requesting more info:

OS:
Processor type and speed:
RAM:
HD size, speed, free space:
Excel version:


Preece

0
 
BurbbleCommented:
This is a bug in the software.

http://support.microsoft.com/default.aspx/kb/270589

You can try the solution there (it's kind of haphazard)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TOC-FriedCommented:
This error means that excel has closed, or the object is no longer active.
0
 
AlphaSquaredAuthor Commented:
Preece:
   OS: Windows XP
   Processor" Penitum 4, 3 gig hz
   RAM: 2 gig (not a misprint)
   HD: IDE 80 gig, 62 gig free not certain of its speed I always get the fastest I can
   Excel 2000 (9.0.6926 SP-3)

   Clearly, we can consider Excel the weakest link in this chain, but this ought to run.

Burbble:
  Your link seems to refer to a bug in:  
       Microsoft Visual Basic 6.0 Professional Edition & Microsoft Visual Basic 6.0 Enterprise Edition
  I've built this with VBA using Excel as a front end.  I don't have a VB Active X DLL.

TOC-Fried:
  Excel remains open.  The code resides (since I only use it for debugging) at the end of the Sub Main so none of my object should come into play at this point.  Maybe some Excel object has gone missing.  

Bottom line.  How do I avoid, fix, or workaround this (short of migrating everything to VB.NET

A

 



   
0
 
TOC-FriedCommented:
Migrating to VB.NET would not assist much with this problem. Not that I know much about .NET programming however.

Excel should have all it's components installed. And you have added excel as a reference correctly I am also assuming.

How much data are you playing with? I've only ever used VB to copy and paste large amounts of data from IE to Excel.
0
 
TOC-FriedCommented:
Worksheets("Data").Range("exitNames").value = Application.Transpose(exitString)

What exactly does this line do? Are you attempting an excel exit?

0
 
AlphaSquaredAuthor Commented:
The line:  Worksheets("Data").Range("exitNames").value = Application.Transpose(exitString)
inserts the values fron the array "exitString" into the the worksheet's neamed range "exit"Names".
0
 
TOC-FriedCommented:
Can this be renamed to eliminate the possibility that my theory EXCEL is actually closing its PID.
0
 
AlphaSquaredAuthor Commented:
Sorry, what's PID?
Even so, most of the time this line works fine.  Why would it close its PID intermittently?
0
 
TOC-FriedCommented:
I know there is a bug with excel when you are working with VLOOKUPs and massive amounts of data that you may have issues with memory limits etc, but that aside, you have named your named range "exit", so I was hoping to see if we can eliminate some misinterpretation of that from the VB compiler by changing its name to something a little more symbolic.

The error means it's disconnected (exited) from the server application (VBA), so as such it thinks Excel is closed and = Nothing.

Cause would seem to be that line, unless you want to send me your full sub and anything around your excel sheet that I may test out and find the issue. Of course everyone else here would have to have the chance to do the same though.

My suggestion to you is to try renaming the "exit" similarities to something much more different, and see what happens.

Check your RAM for errors using memtest86 if you have that lying around as well.
0
 
AlphaSquaredAuthor Commented:
I changed the range name in the Excel worksheet from "exitNames" to "xxxxNames" and continue to have the same problem.
0
 
TOC-FriedCommented:
Would you like the VBA to be converted to a VB6 exe and to be fully debugged? I can do this for you, I will need access to your source and the xl sheet however.

0
 
AlphaSquaredAuthor Commented:
I think I've found the problem.  If any of the strings in the array happens to contain more characters than a cell in Excel can fit things go wonky.  If I restrict the number of characters everything seems to work fine.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now