CODEWITHSUNDEEP
excelvbaobjectautomation
Chris Melville
Chris Melville

Reputation: 1518

Excel VBA automation error with new application object

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.

Upvotes: 1

Views: 2069

Answers (1)

Toddleson
Toddleson

Reputation: 4467

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.

Upvotes: 2

Related Questions