asked on

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

AlphaSquared

ASKER

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???

Preece

Requesting more info:

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

Preece

SOLUTION

Burbble

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

TOC-Fried

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlphaSquared

ASKER

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

TOC-Fried

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.

TOC-Fried

Worksheets("Data").Range("exitNames").value = Application.Transpose(exitString)

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

AlphaSquared

ASKER

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

TOC-Fried

Can this be renamed to eliminate the possibility that my theory EXCEL is actually closing its PID.

AlphaSquared

ASKER

Sorry, what's PID?
Even so, most of the time this line works fine. Why would it close its PID intermittently?

SOLUTION

TOC-Fried

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlphaSquared

ASKER

I changed the range name in the Excel worksheet from "exitNames" to "xxxxNames" and continue to have the same problem.

TOC-Fried

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.

AlphaSquared

ASKER

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.