# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Automation Error in Excel VBA Macro

## davidwe

Thanks in advance for any guidance!  I have a VBA macro in an Excel sheet that gives the following error when it loops after a few times, which has me stumped:

Run-time error '-2147417848 (80010108)': Automation error.  The object invoked has disconnected from its clients.

The application flow works like this:
- For next country in array
-- Clear the spreadsheet
-- Load data from database into spreadsheet
-- Do lots of processing on data
-- Do a save as on this current file (to create country-specific versions)
- Next

This iterator works fine for every country in the loop individually, and even for 2 or 3 in a sequence.  But invariably, it crashes with that error (after which I must force quit Excel to exit) after the 5th iteration.  My internet research on the error has described problems caused by early binding when Excel is opened from a VB script calling it from outside, but since my entire codebase is contained within 4 modules in a single Excel workbook, that seems not to be relevant to my problem.

I have verified that all the database connections are closed after I use the recordset, and there are no file objects referenced except the save as command, ActiveWorkbook.SaveAs filename:=fileRoot.  And all the modules have Option Explicit at the top.

Thank you!

----------


## JBeaucaire

When you DEBUG at the point of the error, what line of code is highlighted?  (that's a rhetorical Q).

This line of code invariably will have some variable(s) that are not what they should be.  Hover your mouse over each variable and note the current value in them...you should be able to spot the errant variable.

----------


## broro183

hi David,

Welcome to the Forum  :Smilie: 

It's reassuring to see that you have option explicit listed at the top of all your modules. However, for helpers to narrow down the potential cause, it would help a lot if we could see the code. From your description, I don't think we need to see any dummy data in the file*, but can you please upload a file that contains all the code?
*hmmm, on second thoughts, I guess some data/layout may be useful to provide context...

Here are some general questions which may help find the cause:
- Do you have any row counters that are declared as integers?
(if so, change them to Long)
- Are you on a stable network (or something like Citrix which may have its moments  :Wink: )?
- I'm not sure how specific you are being when you say "file objects". Automation errors may occur due to a variety of objects not being fully explicitly qualified (possibly all the way to Application level^), for example, workbook/worksheet/sheet/range/cells. We'll be able to point out any examples in your sample file.
- A long shot, is it possible your database connection is timing out?
- For other more experienced helpers, what type of database connection are you using?
(should also be clear in the sample file)
-Do either of the below links help?
^http://support.microsoft.com/default...;en-us;Q319832 or http://www.mrexcel.com/forum/showthread.php?t=7740

hth
Rob

----------


## davidwe

Thank you both for the responses so far.

A few answers:
- The offending line is Selection.Delete Shift:=xlUp.  It is in a procedure that has all of three lines:




```
Please Login or Register  to view this content.
```


I am using Excel 2010, so there are indeed 1048576 rows, though only the first few thousand are in use; it's a little lazier than finding the last in use row, but in theory, should work?  Regardless, I tried a lower constant, 10000, and found the same problem.

I have no integers, only longs.  

I am on a stable network.  But the offending procedure does not have any db connection in it.  But here is the connection string I use: 




```
Please Login or Register  to view this content.
```


I've attached the code sample from the offending module; apologies for the incomplete commenting, but that was my next step!

I've kicked off "spawn"; it runs fine, but errors on createSystemGenerated for Netherlands; if I comment out the first few countries so that Netherlands is the first, it then runs fine on Netherlands & errors out on Taiwan.

----------


## davidwe

And I should add - I did go through the Microsoft link, at http://support.microsoft.com/default...;en-us;Q319832, to no avail.  I'll check out the Mr. Excel link first thing tomorrow morning.  Thanks again!

----------


## JBeaucaire

As per forum rules, be sure to edit that post above and put proper code tags around the code (example in my signature).

This is just as easy for clearing all the data without the brute force deletion:



```
Please Login or Register  to view this content.
```


If there's no formatting to speak of, you could even use the least brutal of all *.ClearContents*

----------


## broro183

hi David,

I've gone through & have prefixed all my suggestions in your code with "'###RB:". I was almost at the end when I noticed your use of:



```
Please Login or Register  to view this content.
```


The Excel Help files state:




> New Optional. Keyword that *enables implicit creation of an object*. If you use New when declaring the object variable, a new instance of the object is created on first reference to it, so you don't have to use the Set statement to assign the object reference. The New keyword can't be used to declare variables of any intrinsic data type, can't be used to declare instances of dependent objects, and can't be used with WithEvents.



What I think we need is purely Explicitly created objects but I need to do some more research...
In the mean time I have added the below code in each function/sub where an rs is Set:



```
Please Login or Register  to view this content.
```


If this isn't the root of your problem, it may be hiding in the code you haven't provided ie "stopSpreadsheetEvents", "findLastPriceIndexRow"*, "CopyDataFromRaw", "FillFormulasDown", "addLookUpValues", "importParameters", "CurrencyFormatString", & "PriceIndexWithStatus".
* I have included a LastCell Function in the attached file.

hth
Rob

----------


## broro183

Here's my extra research...
It's not definitive but the below pages all close the connections & re-Set the connections to Nothing, & although they don't actually state why, I think it will be a good change to make in your code.

Two from Ken Pul's:
http://www.excelguru.ca/node/18
http://www.excelguru.ca/node/23

http://www.exceltip.com/st/Import_da...Excel/427.html
http://www.xlmacros.com/content/how-...events-ado-vba

http://www.aspfree.com/c/a/Database/...m-Excel-VBA/1/ which also mentions:




> Keep in mind that anytime before you close the ADO connection, you can use it to open additional recordsets or perform any other functions. Again, this procedure is really only for demonstration purposes, but you could certainly use it if you had multiple features that needed to obtain recordsets.



As my googling heads off on a tangent...
the below appears to generate a "kill switch" if the macro hangs: http://www.3types.com/?p=12

hth
Rob

----------


## romperstomper

Is there a reason for not using CopyFromRecordset or GetRows to put the recordset onto the worksheet? It will be a lot faster than looping cell by cell.
There's also the small point that you are using _beta_ software...  :Wink:

----------


## davidwe

Everyone, Thank you all for such great advice and tips.  Rob, It turns out your suggested fix did the trick: the problem went away by simply adding:



```
Please Login or Register  to view this content.
```


So, THANK YOU - I am very grateful!

I also wanted to learn from the additional work you did when you mention you went through my code making additional changes, but I didn't see any in the View Code page of the automation error.xls sheet you had uploaded.

As for some of the other questions, suggestions: I simply was not aware of CopyFromRecordset or GetRows, but sound like they would've saved me a lot of coding and are probably much more efficient & robust than what I've written, so I'll be taking advantage; thanks for the tip.  And I'll also be revisiting clearing all the way to that last row, what with the better ways to find the last row (and the better use of clear rather than delete).  

So, again, thanks to everyone for not only solving the specific problem, but pointing out additional ideas to improve the code.

----------


## broro183

hi David,

I'm pleased we could help  :Smilie: 
Edit1: Thanks for adding to my Rep  :Smilie: 
If you are happy, can you please mark the thread as Solved (see Rules for instructions)?





> ...I also wanted to learn from the additional work you did when you mention you went through my code making additional changes, but I didn't see any in the View Code page of the automation error.xls sheet you had uploaded.
> ...



 :Confused: 
Ahhh!
From your use of "View Code page" I think I understand...
Are you rightclicking on the sheet tab & choosing "View Code"?
This approach show's the particular Sheet Module which I left empty & should stay empty unless you are using event macros for the sheet. see Chip's page for explanation on Module types (the page gets "heavier" the further you read!), everything is relevant down to the "Code Names" section: http://www.cpearson.com/excel/codemods.htm 

To view your code which I commented, you can press [alt + F11] to open the VBE, [ctrl + r] to show the Projects Tree (often/usually on the left of the window), (double) click on the relevant Project to expand it, (double) click on the Modules folder to expand it, & (double) click on each module to see what's inside it.
Edit2: Some of my suggestions are probably going to be irrelevant after you include Romperstomper's more fundamental changes.

hth
Rob

----------


## Sie.Peter

I have had the same problem as Dave described. Automation error when executing a rows Delete Shift:=xlUp. in 2010 Beta.

Excel aborted and recovered after this. When the application was started again the sheet was left in a state where it was impossible to double click certain cells as the sheet was suddenly protected beyond the normal deliberate protection it is left with after processing. When unprotecting the sheet and running a small job on that particular sheet it was again impossible to perform a double click due to a protection status I could not remove.

The sheet was therefore unusable and I had to revert to an earlier version where I performed a Delete without the Shift:=xlUp. I have not seen the Automation since then - in three attempts.

In prior incidents I had odd lines drawn as thin and bold across another sheet in the same workbook. The lines were placed across many cells CENTERED in the cells both horizontally and vertically. Horizontally multiple bold lines were drawn in rows 2 and 3, vertically a single thin line was drawn centered in column A. The workbook was no longer usable and my code performed totally eratically. I am at a loss to understanding if this had anything to do with the automation problem, but it happened minutes before I saw the first Automation error. Sorry, I cannot be more specific.

I am not connecting to anything at all in none of my 27,000 lines of code in four modules.

I am looking forward to the release of the Official commercially available 2010 Excel. Any advice is welcome.

Regards,
Peter Sie.

----------


## shg

Welcome to the forum, Sie.Peter.

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.

----------

