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

activexcexcelvba

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

Best Answer

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?

Related Solutions

Excel VBA Run-time Error ‘32809’ – Trying to Understand it

In my case following helped:

Save file as .xlsx (macro-free) - all macros would be erased while saving;
Open source file with macros and copy modules to the .xlsx file;
Save file as .xlsm - full recompile performed.

Afterwards everything started working normally. I had file with 200+ sheets and 50+ macros and posting comments in each module didn't help, but this solution worked.

Excel – SQL Excel VBA Run-time Error 3709 Invalid Connection

First (and unrelated to your error), unless you need to support clients using Windows 2000 or earlier, you should reference the highest Microsoft ActiveX Data Objects version instead of 2.0. If you're only using ADODB to interact with the database, you don't need the Microsoft Access 16.0 Object Library at all.

Second, if you already have a reference, don't create late bound objects like this:

Set cn = CreateObject("ADODB.connection")

Adding the reference early binds the type, so explicitly declare them and instantiate them using New:

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

Your connection string should be fine - where you run into problems are these 2 lines:

    Set rs1 = CreateObject("ADODB.recordset")

    rs1.activeconnection = cn

Executing an ADODB.Command will return the Recordset, not the other way around. Remove those 2 lines entirely. Instead of attaching the connection to the Recordset, you need to use it when you're building your ADODB.Command:

    Dim strSQL As New ADODB.Command
    strSQL.ActiveConnection = cn      '<---Insert this.
    strSQL.CommandText = "SELECT * FROM Table1"
    strSQL.CommandType = adCmdText

Also, get rid of the Hungarian notation there - it's confusing as hell. An ADODB command isn't a String, so why should it be named strFoo?

You also need to clean up after yourself - don't leave your recordset and connection just hanging open when you're done with them. Call .Close when you're finished.

Finally, your SQL statement is most likely incorrect - you probably need to enclose your TID in single quotes('):

"SELECT * FROM Tooling WHERE TID='BD0001'"

It should look closer to this:

Sub Importfromaccess()
    Dim Path As String
    Path = "C:\Users\myUser\Desktop\Database1.accdb"

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Path & ";"

    Dim query As New ADODB.Command
    query.ActiveConnection = cn
    query.CommandText = "SELECT * FROM Tooling WHERE TID='BD0001'"
    query.CommandType = adCmdText

    Dim rs1 As ADODB.Recordset
    Set rs1 = query.Execute ' This is the line the error occurs on

    Sheets("Calc").Range("K1").CopyFromRecordset rs1

    'CLEAN UP AFTER YOURSELF:
    rs1.Close
    cn.Close 
End Sub

Best Answer

Related Solutions

Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Excel – SQL Excel VBA Run-time Error 3709 Invalid Connection

Related Topic