Excel VBA automation error with new application object

Question

I have had a complex project work just fine for many years.

However recently I have started getting the following runtime error:

Microsoft Visual Basic

Run-time error '-2147319765 (8002802b)':

Automation error Element not found

I have boiled this down to the simplest possible sub, as follows:

Sub test()
    Dim xl As Excel.Application
    Set xl = New Excel.Application ' the error occurs on this line
End Sub

References are as per the standard installation. For some reason the system doesn't allow me to add a screenshot. This is now with VBA version 7.1 - I don't know if that makes any difference. This is a corporate environment, with many limitation on what staff can do: so I'm unable to install a new .ddl etc.

Does the code run from a different Office application (Word, Powerpoint...)? Else I see no point in opening a new application object. — FunThomas
– FunThomas, Commented Apr 28, 2021 at 15:47
@FunThomas - as stated, the above code is the simplest possible example to demonstrate the error. The real thing is a whole lot more complex. And yes, a new Application object is definitely needed. — Chris Melville
– Chris Melville, Commented Apr 28, 2021 at 15:51
Do you have any code that is executed automatically when Excel is opened (eg via AddIn?) — FunThomas
– FunThomas, Commented Apr 28, 2021 at 15:57
Try Dim xl as Object and Set xl = CreateObject("Excel.Application." & CLng(Application.Version)) The number at the end is the version of excel you have installed and want to open. 11 is Excel 2003, 12 is Excel 2007 and 16 is the recent stuff. — Toddleson
– Toddleson, Commented Apr 28, 2021 at 17:26
That is good to hear. And I have posted it as an answer instead. Thanks! — Toddleson
– Toddleson, Commented Apr 30, 2021 at 16:01

Toddleson · Accepted Answer · 2021-04-30 16:00:56Z

2

Dim xl as Object 
Set xl = CreateObject("Excel.Application." & CLng(Application.Version))

This is just an alternate method of opening a new Excel application but it fixes the issue of a user having multiple versions of Excel installed by specifically calling out the version you want to open.

It would usually be written like "Excel.Application.12" where number at the end is the version of excel you have installed and want to open. 11 is Excel 2003, 12 is Excel 2007 and 16 is the recent stuff.

I replaced that number with Application.Version to say open the version you already are using.

answered Apr 30, 2021 at 16:00

Toddleson

4,4852 gold badges9 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA automation error with new application object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related