What causes Visual Basic Run-time error -2147319765 (8002802b) in Excel when an ActiveX control has been instanced?

Question

I have created an ActiveX control using C++. I use Visual Basic code to instance the control in an Excel worksheet. I can only run the VB script once, subsequent runs cause the following runtime error when attempting to access the 'ActiveSheet' variable:

Microsoft Visual Basic

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

Automation error
Element not found

I am trying to work out what causes this error and how I can fix it?

As an experiment I tried creating a simple ActiveX control generated by Visual Studio wizards (in both VS 2005 & 2008). I didn't add or modify any code in this test case. The simple test case still causes this error.

Other ActiveX controls in the system don't cause this error (eg I tried instancing 'Bitmap Image') from VB code.

This is the VB code (a macro that I recorded, but hand-coded VB has the same issue):

Sub Macro1()
    ActiveSheet.OLEObjects.Add(ClassType:="test.test_control.1" _
        , Link:=False, DisplayAsIcon:=False).Select
End Sub

Can anyone give me an answer on this? Alternatively any pointers to resources that may help will be appreciated.

Thanks

Solution 1

After talking to Microsoft I found out the cause of the problem I was having.

When creating an ActiveX control using the VS 2005/2008 wizard you need to check the 'Connection points' check box in the 'Options' page. This adds, among other things, IConnectionPointContainerImpl as a base class for your ATL class, which in turn implements IConnectionPointContainer.

Failure to do this means that you can't insert your ActiveX control into an Excel document via Visual Basic more than once. The second time you execute the script you start getting the 'automation errors'.

The answer to the problem was simple enough and it worked, although I am still not sure how it actually relates to the 'automation error' and leaves me wondering why the error messages are not more informative.

Solution 2

You have created an "unqualified" reference to an Excel application that you cannot release by utilizing a Global variable intended for VBA that should not be used in VB 6.0.

This is an unfortunate side-effect of using VB 6.0, but it is the only problem I know of using VB6, and it is easily fixed.

The problem in your case stems from using the 'ActiveSheet' global variable. When using VBA, this is fine, but when using VB 6.0, you must avoid this or else you create an Excel application that you cannot release. This approach will run fine the first time, but will cause all kinds of undefined behavior the second time your routine runs.

In your example, the code should do something like this:

Sub Macro1()
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application

    xlApp.ActiveSheet.OLEObjects.Add(ClassType:="test.test_control.1" _
        , Link:=False, DisplayAsIcon:=False).Select

    ' Then when done:
    xlApp.Quit()
    xlApp = Nothing
End Sub

For a detailed discussion about how to handle this in general, see:

VB 6.0 Tutorial - Finding and Repairing Unqualified References (http://www.xtremevbtalk.com/showthread.php?p=900556#post900556)

For Microsoft documentation on this issue see:

Excel Automation Fails Second Time Code Runs (MSKB 178510) (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q178/5/10.asp)

Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic (MSKB 319832) (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q319832&)

Edit: Note that using html 'a' tags were not working with these links for some reason. Someone might need to look into the parser?

Comments

[romu]

Hi Mike, thanks for the response. Although it has been very useful and the links you provided very informative I still get the same error. Note that I can't use your code sample exactly as it creates a new Excel app object and I want to reuse the current app object. Following is my new test code.

[romu]

Option Explicit Sub Macro1() Dim xlApp As Excel.Application Set xlApp = GetObject(, "excel.application") xlApp.ActiveSheet.OLEObjects.Add(ClassType:="test.test_contr‌​ol.1" _ , Link:=False, DisplayAsIcon:=False).Select Set xlApp = Nothing End Sub

[romu]

Ok, that didn't exactly format very well. What I am doing is grabbing the existing excel object and using it to access 'ActiveSheet'. I still get the same problem however. I can only run the script successfully the first time. Every other time produces the automation error.

[Vahid]

I've never seen it persist once the corrections are put into place. :-( Is this the only point in your code where you are controlling Excel? If not, then you should create a new "Automation Prophylactics" module as explained here: xtremevbtalk.com/showthread.php?p=900556#post900556

[Vahid]

By dropping in the "Automation Prophylactics" module you will be able to find any other unqualified references in your code so that you can correct them. (But read the article, which explains.)