Closed Thread
Results 1 to 13 of 13

Automation Error in Excel VBA Macro

  1. #1
    Registered User
    Join Date
    03-10-2010
    Location
    San Francisco
    MS-Off Ver
    Excel 2010
    Posts
    4

    Automation Error in Excel VBA Macro

    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!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: 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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Automation Error in Excel VBA Macro

    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
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  4. #4
    Registered User
    Join Date
    03-10-2010
    Location
    San Francisco
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Automation Error in Excel VBA Macro

    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.
    Attached Files Attached Files
    Last edited by davidwe; 03-11-2010 at 05:43 AM.

  5. #5
    Registered User
    Join Date
    03-10-2010
    Location
    San Francisco
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Automation Error in Excel VBA Macro

    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!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Automation Error in Excel VBA Macro

    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

  7. #7
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Automation Error in Excel VBA Macro

    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
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Automation Error in Excel VBA Macro

    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

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Automation Error in Excel VBA Macro

    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...
    Remember what the dormouse said
    Feed your head

  10. #10
    Registered User
    Join Date
    03-10-2010
    Location
    San Francisco
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Automation Error in Excel VBA Macro

    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.

  11. #11
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Automation Error in Excel VBA Macro

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

    Quote Originally Posted by davidwe View Post
    ...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
    Last edited by broro183; 03-12-2010 at 06:22 AM.

  12. #12
    Registered User
    Join Date
    04-26-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Smile Re: Automation Error in Excel VBA Macro

    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.

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Automation Error in Excel VBA Macro

    Welcome to the forum, Sie.Peter.

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

    Thanks.
    Entia non sunt multiplicanda sine necessitate

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1