Automation error как исправить

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.

Источник

Automation error ошибка vba

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

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

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

Источник

VBA Automation Error — possibly born from my complete lack of understanding with Error Handling

RockandGrohl

Well-known Member

Hi all, same kind of topic again. Going to start with a top-to-bottom list of nomenclature

Super Automation — Main workbook which contains a list of newspapers and their requirements, houses all macro code
Automation Hub — Where products are placed and ranked according to various criteria
Advert Data — CSV database containing a list of all products and all advertisements, one line per (this is now approaching 30,000 lines)
Various supplementary files that aid in the Automation Hub such as Regional Press Report, Weekly report, etc.

And here’s what happens:

• We have approximately 600-650 newspaper titles each week. A title is selected and SUPER AUTOMATION is initiated.
• Information is brought into a temp sheet and a progress box is opened to display current progress.
• Advert Data is opened and a list of suitable products is found
• The products are sent to Temp sheet, then loaded into Automation Hub
• Various supplementary sheets are opened to aid Automation Hub into determining the best product for the newspaper
• The products are ranked and sent back to the temp sheet, Automation Hub then closes.
• Now that the rank is ascertained, the top X products as required for the advert are allocated
• The Advert Data is opened and the newspaper information is added to that product
• The Advert Data is then saved and closed
• The Super Automation saves and that’s one loop completed out of 600+

The problem:

Somewhere within those red lines, I am getting an error message pop up that crashes Excel. It says «Automation Error» and that’s it. No code, the VBA window doesn’t display where the error is, and if I press «OK» Excel just closes and reopens. I can then press SUPER AUTOMATION again and it will continue on as if nothing happened. It will carry on for 1, 2, 5, 10 or even 100 iterations, then it will crash again, without warning and with no explanation.

Sometimes the Macro will just stop running silently and I still have full control of the sheet, so I can restart it again. This is what leads me to believe it’s an on error.

Solutions I’ve tried:

• Removing all macros from Automation Hub.xlsm and saving to .xlsx
• Doing all Office Updates
• Running files as Admin
• Keeping the Super Automation file on the system drive instead of a network drive
• Crying
• Stepping through code line by line (and it works perfectly)
• Stepping through code in portions (and it works perfectly)
• Praying
• Commenting out «On Error _____» segments, but there are situations outside of my control, like looking for a sheet that may be missing.

I think it may be my poor usage of On Error. In summary, I want this sheet to run overnight, so if there’s a problem with any one of the 600+ rows, I’d rather it just skips that row and I can deal with it in the morning. I’d rather it does 400 with 200 skipped errors, instead of getting to row 30 then copping out and I then have to do another 570.

So in any places where I think there could be an error (for example, no applicable products to transfer from Advert Data to Temp sheet) I want it to «GoTo Skip» and just skip over everything, the «Skip:» portion is literally just return to main tab, iterate to the next line down, repeat.

Below is my code, and I think the error is manifesting itself in the «Call: Super Automation» portion, so I’ve posted that too. Sorry for the mammoth posting.

Источник

Automation error ошибка vba

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

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

Источник

Automation error ошибка vba

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I have worked on a file for long time using Excel 2003. I have few macros and custom functions written. I decided to use another computer to do some additional work on this file — mainly new formulas and outline, but no code writing. The file was opened as 2003 and was saved in the same fashion, was not converted to 2007 at any point.

I saved it and closed it. I then opened it on my original computer and after opening it gave me «Automation Error — Catastrophic Failure» message without an error number. Right after that it sent me to VBA screen and instead of having my file name under the Project Tree, it has «VBAproject». I click on it and can see my code and my forms, but the worksheets are not label properly plus they have this little blue icon next to them.

I tried to open the file again by clicking Disable macros, and no error was given but obviously can’t do much with the file.

One thing I read around is about sharing and references. This file sharing options were turned off so that’s not it. I then realized under VBA-Tools-References that the office 2007 is referencing different files for some of the selections, like Visual Basic For Applications, Microsoft Excel 12.0 Object Library, etc. I copied those files to the proper folders on the 2003 computer but that didn’t do the trick. When I click disable macros and go to check the references in the 2003 version everything looks normal.

Any ideas? I did quite of work on this file so redoing this is the last resort but I hope I don’t have to go that route.

Thank you in advance!

Automation errors usually occur when a workbook is trying to run some macro that contains objects they are not included in the references section of the VBA editor. One example that would produce this is having some controls on a form which require a specific DLL to be correctly registered on a client PC. If these DLLs are on your pc, the workbook is fine. Put this same workbook on another PC without the DLLs, and an automation error will occur.

Hope this helps.

Thank you Jennifer,

So tell me what is the best way to check what dll is crashing? Should I simply check which files are referenced in the VBA-Tools-References and see of those files are on the other computer?

Whilst it might be a reference issue what you describe seems much more serious than the usual problems associated with a missing or incompatible reference.

It’s not clear from your original description which Excel version you are currently using, though I assume 2007 as you mentioned «12.0» in the references. FWIW those references are normal, indeed all project will include the following as default

Visual Basic For Applications
Microsoft Excel x.0 Object Library
OLE Automation
Microsoft Office x.0 Object Library
and possibly if a form has ever been added —
Microsoft Forms 2.0 Object Library

where x refers to the Excel version.

Can you open the file in Safe mode, from the Run command
«excel.exe /safe» without quotes
(you might then need to change security settings via the UI to open a file with VBA)

Can you open the file in Excel 2003

Is there anything you can think of in the file that’s not typical about the xls/a(?) originally saved in 2003.

Regards,
Peter Thornton

Hello Peter and thank you for the response.

I am opening the file with Excel 2003 (on Windows XP if it matters). I have worked on this file for long time and just recently did some cosmetic work on Excel 2007 on another computer. I did some VBA coding at that point but it was minor and primarily for formatting help.

The issue is that now I open the file in 2003 and gives me the error. What is even more absurd, I re-worked an old file (which was never opened on 2007) and that file still gives the same error on few of my company’s computers that are all running on 2003.

The current references look like this:

Visual Basic For Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library

I was able to uncheck the 3rd and 4th of these but still the file gives an error. When I open the file on 2007 the 11.0 becomes 12.0 and no other changes.

I can’t think of any special references since all my work has been done on 2003 with few minor changes in 2007. This error drives me crazy since I can’t do anything with the file. I will try this safe mode opening. I have never opened a file through VBA but will try.

When you round trip a VBA file saved on an old version to a new, and back again reference problems can be introduced, even if you haven’t done anything apart from save.

That occurs when the reference has been updated to the new version then not found in the old version’s system. Whilst that can make some VBA stuff fail, normally not with the serious consequences you described. However the ref’s you show this time look fine for the file when opened in 2003, indeed exactly what I’d expect. Actually I’m surprised you were able to remove two of the ref’s.

The cosmetic changes you made in 2007 sound like formats, some of these may cause problems when reopened in 2003, though normally the compatibility checker will pick these up.

I’m a bit confused though, if I follow you are saying you’ve got problems with a file that’s never even been opened in 2007. Also if I follow the exact same file opens fine in some 2003 systems but not in others. Does the file include any non standard forms controls, any controls that you would have needed to add to the toolbox for example.

Regards,
Peter Thornton

Sorry to confuse you here — I am talking about 2 files.

First file — was used in 2003, then saved and more formatting work was done on 2007, after that this file crashed on my personal computer and one my coworker’s as well. We can says this «round-trip» is the cause.

The second file is technically a copy of the first before it was opened on 2007. I re-did all of the work that I previously did on 2007 on my 2003 excel and that file is works fine on my computer but is crashing on some of my coworkers computers. This file was never opened on 2007.

Got me thinking though — I have loaded some custom toolbars on my excel that I am not sure all people within my company have. I don’t know if I am going in the wrong direction here. Still I got a file now that I can open along with 2-3 other co-workers, but majority of the people can’t and this file was not opened on 2007 but the error (at least the message) is exactly the same as the error I myself was getting when doing this 2003-2007 round trip.

Curiouser and curiouser!

Do you have an original copy of the file that you neither modified in 2007 nor 2003, if so does that work on all machines.

Are there any Links ?

Any non standard controls that would have needed to be added to the Toolbox ?

Can you open the problematic files on the problematic machines in Safe Mode (see my first post) ?

Any references marked MISSING ?

Any Open event code, if so hold Shift when opening.

Custom Toolbars, do you mean «attached» to the workbook or created on demand. FWIW I really think best never to «attach» custom toolbars, instead create the custom toolbar in the workbook’s open event and delete in the close. That said I can’t think of any reason for a custom toolbar to cause the problems you described.

Regards,
Peter Thornton

Glad I got your curiosity! 🙂

The file I worked on from scratch over a year now. It was always done on 2003.

If you mean links to outside sources — no. There are hyperlinks within the file itself, but nothing fancy on that end.

I don’t think any non-standard controls are added to the Toolbox.

I can open even the file that crashes on my computer by selecting Disable Macros. I tried the safe mode as well with the file that always crashes and besides the message that the macros are disabled in the beginning everything else is fine. So I assume all my coworkers would be able to open it with Safe mode as well.

No MISSING references. Unless it is hiding somewhere else, but I would assume the error message would let me know that.

No Open event code — that includes all of the tabs as well and no change event code.

The custom toolbars are widely used by my company and never caused many errors. But you are right, I don’t think that is the error.

I would gladly send you the file, but it is full with company information. What is even more annoying is that I work in a different location from the rest of my coworkers that have issues with this file so I can’t see what is going on there.

Before I did the changes with 2007, a month ago I had this issue — after my regular update on the file, the other person tried to open the file but got the same error. This was the first time when we faced that error. While waiting for him to hear from IT, I did some more updates to the file (didn’t remove anything old and didn’t add any code) and sent him the most recent copy. Suddenly it worked for him. Bizarre! 2 weeks later I did the updates on 2007 and I got the same error myself. For a year working with this file neither of us had any problems. It just recently started. I think it must be something not necessary related to this file, but rather with my excel, which was activated by another file and this file doesn’t like it. Is that too crazy of an idea?

Источник

Laider Пользователь Сообщений: 127 Регистрация: 04.12.2017	Всем привет. Кто сталкивался подскажите, как можно отловить эту ошибку. Часто встречается в офисе но не у всех. Там есть, там нет. Макросы работают одни и те же. Причем ошибка появляется без каких-либо пояснений и предложений к отладке.

Alemox Пользователь Сообщений: 2183 Регистрация: 25.02.2013

Проблему надо искать в макросе, а не в компьютере. Если у вас макрос работает с несколькими книгами, то возможно, что он теряет фокус нужной книги. Сам несколько раз сталкивался с таким. Неправильно прописан код. Например вместо ActiweWorkbook нужно было использовать Thisworkbook и прочее. Если явно не прописаны какие-то имена листов и книг. Или если название макроса совпадает с названием макроса в другой книге. Но ошибку надо искать именно в макросах. Может макрос на открытие книги чего не так делает. Думаю, что теряется макрос при выполнении кода. Мастерство программиста не в том, чтобы писать программы, работающие без ошибок. А в том, чтобы писать программы, работающие при любом количестве ошибок.

Laider Пользователь Сообщений: 127 Регистрация: 04.12.2017	Сегодня нашел проблему в одном из таких. Сборщик из нескольких книг. Заменил копирование диапазонов выделением, на указание конкретных его границ. Проблема исчезла. Знал бы сразу, сразу бы так и писал код. Очень странно что на одном ПК все работает, а на другом нет. Теперь надо проверять все старые схожие макросы где использовал Selection.

Dima S Пользователь Сообщений: 2063 Регистрация: 01.01.1970	Для сбора из нескольких книг использовать Select вообще не обязательно (даже нежелательно, ибо тормозит). Поищите тут на сайте полно реализаций сбора данных из нескольких книг.

Karataev Пользователь Сообщений: 2306 Регистрация: 23.10.2014	#5 24.03.2018 10:51:42 Laider, мне кажется, что Вы неправильно назвали тему. Вы хотите не отловить, а понять причину ошибки. А вообще, чтобы отловить ошибку, нужно использовать код ниже. Хотя припоминаю, что для каких-то ошибок это не работает (не могу сейчас вспомнить). Скрытый текст Изменено: Karataev — 24.03.2018 10:52:51

Определение «Automation error»

Люди часто предпочитают ссылаться на «Automation error» как на «ошибку времени выполнения», также известную как программная ошибка. Когда дело доходит до Windows Operating System, инженеры программного обеспечения используют арсенал инструментов, чтобы попытаться сорвать эти ошибки как можно лучше. Тем не менее, возможно, что иногда ошибки, такие как ошибка 440, не устранены, даже на этом этапе.

В выпуске последней версии Windows Operating System может возникнуть ошибка, которая гласит: «When you access Automation objects, specific types of errors can occur.». Когда это происходит, конечные пользователи могут сообщить Microsoft Corporation о наличии ошибок «Automation error». Затем они исправляют дефектные области кода и сделают обновление доступным для загрузки. Эта ситуация происходит из-за обновления программного обеспечения Windows Operating System является одним из решений ошибок 440 ошибок и других проблем.

Что вызывает ошибку 440 во время выполнения?

В первый раз, когда вы можете столкнуться с ошибкой среды выполнения Windows Operating System обычно с «Automation error» при запуске программы. Мы можем определить, что ошибки во время выполнения ошибки 440 происходят из:

Ошибка 440 Crash — Ошибка 440 является хорошо известной, которая происходит, когда неправильная строка кода компилируется в исходный код программы. Как правило, это результат того, что Windows Operating System не понимает входные данные или не знает, что выводить в ответ.

Утечка памяти «Automation error» — ошибка 440 приводит к постоянной утечке памяти Windows Operating System. Потребление памяти напрямую пропорционально загрузке ЦП. Потенциальным фактором ошибки является код Microsoft Corporation, так как ошибка предотвращает завершение программы.

Ошибка 440 Logic Error — Вы можете столкнуться с логической ошибкой, когда программа дает неправильные результаты, даже если пользователь указывает правильное значение. Он материализуется, когда исходный код Microsoft Corporation ошибочен из-за неисправного дизайна.

Как правило, ошибки Automation error вызваны повреждением или отсутствием файла связанного Windows Operating System, а иногда — заражением вредоносным ПО. Большую часть проблем, связанных с данными файлами, можно решить посредством скачивания и установки последней версии файла Microsoft Corporation. В некоторых случаях реестр Windows пытается загрузить файл Automation error, который больше не существует; в таких ситуациях рекомендуется запустить сканирование реестра, чтобы исправить любые недопустимые ссылки на пути к файлам.

Типичные ошибки Automation error

Типичные ошибки Automation error, возникающие в Windows Operating System для Windows:

• «Ошибка программы Automation error. «
• «Недопустимая программа Win32: Automation error»
• «Automation error столкнулся с проблемой и закроется. «
• «Не удается найти Automation error»
• «Automation error не может быть найден. «
• «Ошибка запуска в приложении: Automation error. «
• «Не удается запустить Automation error. «
• «Отказ Automation error.»
• «Ошибка в пути к программному обеспечению: Automation error. «

Обычно ошибки Automation error с Windows Operating System возникают во время запуска или завершения работы, в то время как программы, связанные с Automation error, выполняются, или редко во время последовательности обновления ОС. При появлении ошибки Automation error запишите вхождения для устранения неполадок Windows Operating System и чтобы HelpMicrosoft Corporation найти причину.

Источники проблем Automation error

Заражение вредоносными программами, недопустимые записи реестра Windows Operating System или отсутствующие или поврежденные файлы Automation error могут создать эти ошибки Automation error.

В первую очередь, проблемы Automation error создаются:

• Поврежденные ключи реестра Windows, связанные с Automation error / Windows Operating System.
• Файл Automation error поврежден от вирусной инфекции.
• Другая программа злонамеренно или по ошибке удалила файлы, связанные с Automation error.
• Другая программа, конфликтующая с Automation error или другой общей ссылкой Windows Operating System.
• Windows Operating System (Automation error) поврежден во время загрузки или установки.

Продукт Solvusoft

Загрузка
WinThruster 2022 — Проверьте свой компьютер на наличие ошибок.

Совместима с Windows 2000, XP, Vista, 7, 8, 10 и 11

Установить необязательные продукты — WinThruster (Solvusoft) | Лицензия | Политика защиты личных сведений | Условия | Удаление

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.