Automation error vba при запуске макроса

VBA Automation Error

In this Article

This tutorial will explain what a VBA Automation Error means and how it occurs.

Excel is made up of objects – the Workbook object, Worksheet object, Range object and Cell object to name but a few. Each object has multiple properties and methods whose behavior can be controlled with VBA code. If the VBA code is not correctly programmed, then an automation error can occur. It is one of the more frustrating errors in VBA as it can often pop up for no apparent reason when your code looks perfectly fine!

(See our Error Handling Guide for more information about VBA Errors)

Referring to a Variable no Longer Active

An Automation Error could occur when you are referring to a workbook or worksheet via a variable, but the variable is no longer active.

When we run the code above, we will get an automation error. This is due to the fact that we have opened a workbook and assigned a variable to that workbook. We have then closed the workbook but in the next line of code we try to activate the closed workbook. This will cause the error as the variable is no longer active.

If we want to activate a workbook, we first need to have the workbook open!

Memory Overload

This error can also sometimes occur if you have a loop and you forget to clear an object during the course of the loop. However, it might only occur sometimes, and not others- which is one of the reasons why this error is can be so annoying.

Take for example this code below:

The variable is declared as an Object, and then the SET keyword is used to assign an image to the object. The object is then populated with an image and inserted into the Excel sheet with some formatting taking place at the same time. We then add a loop to the code to insert 100 images into the Excel sheet. Occasionally this causes an automation error, but sometimes it doesn’t – frustrating, right?

The solution to this problem is to clear the object variable within the loop by setting the object to NOTHING – this will free the memory and prevent the error.

DLL Errors and Updating Windows

Sometimes the error occurs and there is nothing that can be done within VBA code. Re-registering DLL’s that are being used, making sure that our Windows is up to date and as a last resort, running a Registry Check as sometimes the only things that may work to clear this error.

A good way of avoiding this error is to make sure that error traps are in place using the On Error Go To or On Error Resume Next routines.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

Источник

Excel automation fails second time code runs

Symptoms

While running code that uses Automation to control Microsoft Excel, one of the following errors may occur:

In Microsoft Excel 97 and in later versions of Excel, you receive one of the following error message:

Error message 1

Run-time error ‘1004’:
Method ‘ ‘ of object ‘_Global’ failed

Error message 2

Application-defined or object-defined error

In Microsoft Excel 95, you receive one of the following error messages:

Error message 1

Run-time error ‘-2147023174’
OLE Automation error

Error message 2

Run-time error ‘462’:
The remote server machine does not exist or is unavailable.

Cause

Visual Basic has established a reference to Excel because of a line of code that calls an Excel object, method, or property without qualifying the element with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than one time.

Resolution

To resolve this problem, modify the code so each call to an Excel object, method, or property is qualified with the appropriate object variable.

Status

This behavior is by design.

More Information

To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application object or the Excel Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when you try to run the automation code multiple times. Note that even if the line of code begins with the object variable, a call may be made to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.

The following steps illustrate how to reproduce this issue and how to correct the issue.

Steps to reproduce the behavior

Start a new Standard EXE project in Visual Basic. Form1 is created by default.

On the Project menu, click References, and then check the Object Library for the version of Excel that you intend to automate.

Place a CommandButton control on Form1.

Copy the following code example to the Code Window of Form1.

On the Run menu, click Start, or press F5 to start the program.

Click the CommandButton control. No error occurs. However, a reference to Excel has been created and has not been released.

Click the CommandButton control again. Notice that you receive one of the error messages that are discussed in the «Symptoms» section.

Note The error message occurs because the code refers to the method of the cell without preceding the call with the
xlSheet object variable.

Stop the project and change the following line of code:

Change the line of code to resemble the following line of code.

Run the program again. Notice that you can run the code multiple times without receiving an error message.

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

167223 Microsoft Office 97 Automation Help file available

189618 You may receive the «Run-time error ‘-2147023174’ (800706ba)» error message or the «Run-time error ‘462’» when you run Visual Basic code that uses Automation to control Word

Источник

Vba automation error что это

Вроде как на ровном месте (т.е. до сегодня пару лет работало) вот такая строчка кода:

Set fs = CreateObject(«Scripting.FileSystemObject»)

стала вызывать вот такую ошибку:

Run-time error ‘-2147024770 (8007007e)’:
Automation error

Собственно, это использовалось для последующей проверки ссуществования файла (FileExists). И вот.
Подскажите, пожалуйста, что тут может быть? Библиотеки слетели? Куда смотреть?

Originally posted by rtttv
[b]Собственно, это использовалось для последующей проверки ссуществования файла (FileExists). И вот…

Так всегда происходит , когда люди пренебрегают родными функциями и используются посторонние библиотеки.

Посмотрите, есть ли ссылки на библиотеки Microsoft Scripting Runtime

Спасибо. Именно это — Microsoft Scripting Runtime! Сбилась (в системе) регистрация библиотеки scrrun.dll. А в Excel (Tools — References) «галка» на неё и не стояла. И сейчас не стоит, но всё работает!Т.е. системные библиотеки сами подтягиваются? А вообще в такой ситуации нужно устанавливать reference?

Так всегда происходит , когда люди пренебрегают родными функциями и используются посторонние библиотеки.

Originally posted by rtttv
[b]А в Excel (Tools — References) «галка» на неё и не стояла. И сейчас не стоит, но всё работает

Это кстати, позволяет избежать проблем, связанных с различными версиями (при передаче файла другим лицам)

что Naeel Maqsudov предлагал . Application.Run

Первоисточник конечно всегда лучше, но это действительно было давно, к тому же, на другом форуме, да и ответ был довольно краток и сводился к тому, что вызывать процедуры можно так :

Источник

Excel automation fails second time code runs

Symptoms

While running code that uses Automation to control Microsoft Excel, one of the following errors may occur:

In Microsoft Excel 97 and in later versions of Excel, you receive one of the following error message:

Error message 1

Run-time error ‘1004’:
Method ‘ ‘ of object ‘_Global’ failed

Error message 2

Application-defined or object-defined error

In Microsoft Excel 95, you receive one of the following error messages:

Error message 1

Run-time error ‘-2147023174’
OLE Automation error

Error message 2

Run-time error ‘462’:
The remote server machine does not exist or is unavailable.

Cause

Visual Basic has established a reference to Excel because of a line of code that calls an Excel object, method, or property without qualifying the element with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than one time.

Resolution

To resolve this problem, modify the code so each call to an Excel object, method, or property is qualified with the appropriate object variable.

Status

This behavior is by design.

More Information

To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application object or the Excel Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when you try to run the automation code multiple times. Note that even if the line of code begins with the object variable, a call may be made to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.

The following steps illustrate how to reproduce this issue and how to correct the issue.

Steps to reproduce the behavior

Start a new Standard EXE project in Visual Basic. Form1 is created by default.

On the Project menu, click References, and then check the Object Library for the version of Excel that you intend to automate.

Place a CommandButton control on Form1.

Copy the following code example to the Code Window of Form1.

On the Run menu, click Start, or press F5 to start the program.

Click the CommandButton control. No error occurs. However, a reference to Excel has been created and has not been released.

Click the CommandButton control again. Notice that you receive one of the error messages that are discussed in the «Symptoms» section.

Note The error message occurs because the code refers to the method of the cell without preceding the call with the
xlSheet object variable.

Stop the project and change the following line of code:

Change the line of code to resemble the following line of code.

Run the program again. Notice that you can run the code multiple times without receiving an error message.

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

167223 Microsoft Office 97 Automation Help file available

189618 You may receive the «Run-time error ‘-2147023174’ (800706ba)» error message or the «Run-time error ‘462’» when you run Visual Basic code that uses Automation to control Word

Источник

Vba automation error что это

прошу помочь!
Выскочила такая «бяка» при запуске ранее рабочего макроса. Подозреваю, что что-то с настройками самого Ёкселя (либо ВБА), поскольку эксельный файл с этим макросом даже не хочет сохраняться («Документ не сохранен»). Хотя файлы с другими макросами вполне работоспособны. Офис-2003, в настройке безопасности доверялки подключены.

зы. Сколько уж зарекался оставлять свой комп коллегам

Automation error (Error 440)

When you access Automation objects, specific types of errors can occur. This error has the following cause and solution:

An error occurred while executing a method or getting or setting a property of an object variable. The error was reported by the application that created the object.
Check the properties of the Err object to determine the source and nature of the error. Also try using the On Error Resume Next statement immediately before the accessing statement, and then check for errors immediately following the accessing statement.

ошибка при установке (получении) свойств обьекта.
рекомендуют использовать On Error Resume Next чтобы в свойствах Err получить более детальную информации о произошедшем.

автору на заметку — это не решит проблемы, но должно поднять настроение. при первом прочтении — я плакал.

Источник

One of the more frustrating errors to occur in VBA is the Automation Error.  It can often pop up for no apparent reason despite your code looking perfect.

Reasons for This Error

There are a variety of reasons that this can occur.

Microsoft Office is made up of Objects – the Workbook object, Worksheet Object, Range object and Cell object to name just a few in Excel; and the Document Object in Word.  Each object has multiple properties and methods that are able to be programmed to control how the object behaves. If you do not program these methods correctly, or do not set a property correctly, then an automation error can occur.

It can be highly annoying as the code will run perfectly for a while, and then suddenly you’ll see this!

You may get a number of different messages for this error.

The Object Has Disconnected from Its Client

An automation error could occur when you are referring to a workbook or worksheet via a variable, but the variable is no longer active.  Make sure any object variables that you are referring to in your code are still valid when you call the property and methods that you are controlling them with.

Excel Error Load Form

An automation error could occur within Excel if you load a form while another object (like the worksheet or range object) are still referenced.  Remember to set your object references to NOTHING after you have finished writing the code to control them with.

Error Hiding/Unhiding Sheets In Excel

There are 3 ways to control the visible property in an Excel sheet in VBA – xlSheetVisible, xlSheetHidden or xlSheetVeryHidden.   An automation error could occur if you are trying to write information to a sheet which has the xlVeryHidden property set – make sure you set the sheet to visible in the code before you try to write anything to the sheet using Visual Basic Code.

Error 429 and 440

If you receive either of these errors, it can mean that your DLL files or ActiveX controls that you might be using are not correctly registered on your PC, or if you are using an API incorrectly.  It can also occur if you are running using 32-bit files on a 64-bit machine, or vice versa. 

Automation Error When Running Macro Enabled Workbook Over A Network

If you have a workbook open over a network, and you get an automation error when you run a macro, check your network connections – it could be that the network path is no longer valid.   Check you network paths and connections and make sure that they are all still working.

ADODB Automation Error

This error can occur when you are programming within Microsoft Access and are using the ADODB.Connection object.  It can be caused by any number of reasons from conflicting DLL files to a registry corruption, or simply that you did not set the recordlist object to nothing when you were finished using it!

Common Causes and Things to Check

Here are some reasons you might be getting the error. Perhaps you are…

• Trying to write information to hidden sheets or cells.
• Trying to write information to other documents or workbooks.
• Referring to objects that do not exist.
• Needing to install an update to Office, or the correct .Net framework.
• Loading objects (like pictures) into memory using a loop, and failing to clear the memory between each load (set Pic = Nothing).
• A Corrupt Registry – this is a hard one, as it often means that you need to remove Office entirely from your machine and then re-install it.  

Ways to Solve It

Error Trapping

Error trapping is one of the best ways to solve this problem.  You can use On Error Resume Next – or On Error GoTo Label – depending on your needs at the time.  If you want the code to carry on running and ignore the error, use On Error Resume Next.  If you want the code to stop running, create an error label and direct the code to that error label by using On Error GoTo label.

Clear the Memory

Another way to solve the problem is to make sure you clear any referred objects either in a loop or at the end of your code.  For example, if you have referred to a picture object, set the picture object to NOTHING, or if you have referred to a Worksheet object, set the Worksheet object to NOTHING once you have run the code for that picture of worksheet.

The code example below will insert a picture into a worksheet.  It may or may not give you an automation error!

Sub InsertPicture()
	Dim i As Integer
	For i = 1 To 100
		With Worksheets("Sheet1")
 		Set shp = .OLEObjects.Add(ClassType:="Forms.Image.1", _
 		Link:=False, DisplayAsIcon:=False, Left:=.Cells(i, "A").Left
		, Top:=.Cells(i, "A").Top, Width:=264, Height:=124)
 		End With
		 With shp
			.Object.PictureSizeMode = 3
			.Object.Picture = LoadPicture("C:dataimage" & i & ".jpg")
			.Object.BorderStyle = 0
			.Object.BackStyle = 0
	 End With
	Next i
End Sub

The variable is declared as an OLEObject, and then the SET keyword is used to assign an image to the object.  The object is then populated with an image and inserted into the Excel sheet – some formatting taking place at the same time. I have then added a loop to the code to insert 100 images into the Excel sheet.  Occasionally this causes an automation error, but sometimes it doesn’t – frustrating, right?

The error often occurs when Excel runs out of memory – assigning an object over and over again without clearing the object could mean that Excel is struggling with memory and could lead to an automation error.

We can solve this problem 2 ways:

1. Set the object to NOTHING after it is inserted each time
2. Add an error trap to the code.

Sub InsertPicture()
On Error Resume Next
	Dim i As Integer
	For i = 1 To 100
		With Worksheets("Sheet1")
 		Set shp = .OLEObjects.Add(ClassType:="Forms.Image.1", _
 		Link:=False, DisplayAsIcon:=False, Left:=.Cells(i, "A").Left
		, Top:=.Cells(i, "A").Top, Width:=264, Height:=124)
 		End With
		 With shp
			.Object.PictureSizeMode = 3
			.Object.Picture = LoadPicture("C:dataimage" & i & ".jpg")
			.Object.BorderStyle = 0
			.Object.BackStyle = 0
	 End With
	Set shp = Nothing
	Next i
End Sub

Make sure your PC is up to date

A third way to solve the problem is to install the required update to Office or Windows, or the latest version of the .Net framework.  You can check to see if there are any updates available for your PC in your ‘Check for updates’ setting on your PC.

In Windows 10, you can type ‘updates’ in the search bar, and it will enable you to click on “Check for updates“.

It is always a good idea to keep your machine up to date.

Run a Registry Check

If all else fails, there are external software programs that you can download to run a check on the registry of your PC.

As you can see from above, this error often has a mind of its own – and can be very frustrating to solve.  A lot of the times trial and error is required, however, writing clean code with good error traps and referring to methods, properties and objects correctly often can solve the problem.

In the last few weeks, when I have tried to run any VBA macro in most situations, I get messages that «Microsoft Excel has stopped working» and «Microsoft Excel is trying to recover your information». When the second message appears, I also get an error message «Automation Error», helpfully behind the Excel window. This shows no error number, though clicking on Help reveals that it is Error 440. The accompanying Help text implies that it is to do with incorrect code setting objects. But the errors are coming up the moment I hit the Run (or even Step) button.

Amazingly enough, if I edit the code, even by putting in or removing a blank line (and even without saving the change), the macro can be executed. So it does not seem to be errors in the code itself.

If the macro is fairly simple and does not do much, I can usually run the next macro. However, if it does a substantial amount of processing I am back where I started. As I won’t have reopened Excel between it running and then not running, it seems unlikely to relate to Excel startup issues.

The problem applies when trying to run all VBA macros in Excel 2010, even simple or dummy ones, and even macros which had not been edited for months or even years. It is as if the VBA code is not closing down or releasing something properly.

As it seemed to start after various Office updates from Microsoft, I tried uninstalling the recent ones but this does not seem to have worked. I have tried opening Excel in safe mode — no difference.

VBA macros in Access 2010 do not seem to be affected. Nor are those in Excel 2003, which I still use for some tasks.

I am using Windows 7 SP2 64-bit. The computer is six years old but good enough spec for Windows 10. I am having problems with minute-long freezes in IE (which may be Flash-related judging from Event Viewer) and occasional crashing of the graphics card but these seem unlikely to be related.

Event viewer invariably tells me…

Faulting Application name: EXCEL.EXE, version 14.0.7168.5000...
Faulting module name: EXCEL.EXE, version 14.0.7168.5000...
Exception code: 0xc0000005
Fault offset: 0x004ef40d

The rest varies of course. Event ID is shown as 1000.

I have trawled the internet for any references to automation error messages with this specific impact but no luck. I have also scanned with Avast and MalwareBytes and not found anything worrying. Has anyone got any ideas please?

1. 03-10-2010, 02:30 PM

   
   

   #1

   

   
   Registered User
   

   

   ---

   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. 03-10-2010, 05:31 PM

   
   

   #2

   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. 03-10-2010, 09:10 PM

   
   

   #3

   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. 03-11-2010, 01:16 AM

   
   

   #4

   

   
   Registered User
   

   

   ---

   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:

   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:

   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.

   Last edited by davidwe; 03-11-2010 at 05:43 AM.

   ---

5. 03-11-2010, 01:18 AM

   
   

   #5

   

   
   Registered User
   

   

   ---

   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. 03-11-2010, 01:24 AM

   
   

   #6

   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:

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

   ---

7. 03-11-2010, 08:46 AM

   
   

   #7

   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:

   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:

   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

   ---

8. 03-11-2010, 09:52 AM

   
   

   #8

   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. 03-11-2010, 11:04 AM

   
   

   #9

   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. 03-11-2010, 11:17 PM

    
    

    #10

    

    
    Registered User
    

    

    ---

    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:

    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. 03-12-2010, 06:14 AM

    
    

    #11

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

    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

    Last edited by broro183; 03-12-2010 at 06:22 AM.

    ---

12. 04-26-2010, 11:36 PM

    
    

    #12

    

    
    Registered User
    

    

    ---

    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. 04-27-2010, 12:18 AM

    
    

    #13

    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

    ---