Automation Error in Excel VBA Macro

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.

hi David,

Welcome to the Forum

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

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:

Sheets("Raw").Select
Rows("2:1048576").Select
Selection.Delete Shift:=xlUp

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:

Global Const cnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=raptorbeta;Data Source=SV1PSVM81;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"

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.

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:

Sheets("Raw").Rows("2:1048576").Clear

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

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:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

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:

        '###RB: can the below line be added to free memory?
        Set rs = Nothing

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

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

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

hi David,

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

Originally Posted by davidwe

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

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

Welcome to the forum, Sie.Peter.

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

Thanks.