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

Answer 1

I was getting a different message, but the behavior was weird, like this problem. Have a look at my question here. In my case, VBA CodeCleaner did the trick.

Answer 2

From the error description and guessing a bit I would suggest you try the following.

• Make sure you can instantiate your ActiveX control elsewhere without problems. This might simply be an issue with instantiation of your ActiveX and not an Excel issue.

Also on a related now, you should use the general "test.test_control" rather than "test.test_control.1" but thats not your issue here.

Answer 3

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?

Answer 4

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.

Answer 5

I had this error with UserControl classes that were implementing interfaces manually, but with no events. The solution in this case was to declare an empty interface for the ComSourceInterfaces attribute:

[ComVisible( true )]
[Guid( "your-guid-here" )]
[ProgId( "progid-here" )]
[ComDefaultInterface( typeof( IMyClass ) )]
[ComSourceInterfaces( typeof( IMyClassEvents ) )]
[ClassInterface( ClassInterfaceType.None )]
public partial class ExcelMap : UserControl, IMyClass
{
    //implementation here
}

[ComVisible( true )]
[Guid( "your-2nd-guid" )]
[InterfaceType( ComInterfaceType.InterfaceIsDual )]
public interface IMyClass
{
     //whatever things you need to define here
}

[ComVisible( true )]
[Guid( "your-3rd-guid" )]
[InterfaceType( ComInterfaceType.InterfaceIsDual )]
public interface IMyClassEvents
{
    //empty interface to avoid Automation errors
    //you can define events here in the normal way, but it's not necessary
}

Answer 6

Adodc1.RecordSource = "select * from Advancedrecord where Date_Advanced ='" & July & "'" Adodc1.Refresh

tis is the coding for show the data from the database...but i cant solve tis problem....