ActiveX component cannot create object 429 Error

[sustorm]

I am using the following code to determine if Excel is open, if it is open use that session of excel to do the following, if it is not open create a new session of excel. I have checked all of my available references and they are all checked and none are listed as missing. I am able to use createobject if it is not in an if statement and same with get object, but together I get the ActiveX component cannot create object 429 Error. Can suggestions on how to correct or another code they may work? thank you. Also i have read some of the knowledge base articles and tried those solutions without result. Thanks

Option Compare Database
'-------------------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------
' Procedure : Send2Excel
' Author    : Bob Larson
' Date      : 5/25/2008
' Purpose   : Send any single recordset form to Excel.  This will not work
'             with subforms.
' Use       : You may freely use this code as long as the author information
'             in this header remains intact
'-----------------------------------------------------------------------------
'
Public Function Send2Excel(frm As Form, Optional strSheetName As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to

    
    Dim rst As DAO.Recordset
    Dim Apxl As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim intCount As Integer
    Dim stDocName As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
 
    Set rst = frm.RecordsetClone
    Set Apxl = GetObject(, "Excel.Application")
    If Apxl Is Nothing Then
    Set Apxl = CreateObject("Excel.Application")
    End If
    
    Set xlWBk = Apxl.Workbooks.Open("N:\EvalCard\evalcardsheet.xls")
        Apxl.Visible = True
        Apxl.Run "first"
    
    Set xlWSh = xlWBk.Worksheets("Sheet2")
    If Len(strSheetName) > 0 Then
        xlWSh.Name = Left(strSheetName, 34)
    End If
        Apxl.Run "second"
    xlWSh.Range("A1").Select
    Do Until intCount = rst.Fields.Count
        Apxl.ActiveCell = rst.Fields(intCount).Name
        Apxl.ActiveCell.Offset(0, 1).Select
        intCount = intCount + 1
    Loop
    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting.
    ' You can comment out or delete any of this that you don't want to
    ' use in your own export.
    With Apxl.Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    Apxl.Selection.Font.Bold = True
    With Apxl.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ' selects all of the cells
    Apxl.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    Apxl.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select
    Apxl.Run "opennewbook"
    DoCmd.Close acForm, "EvalCard"
    rst.Close
    Call sTestSleep
    DoCmd.OpenForm ("EvalCard"), acNormal
    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number, Erl, vbCritical
    Exit Function

       
End Function

 

[boblarson]

Personally, I just use the CreateObject. Why worry about using an existing Excel object anyway. It isn't going to hurt anything to create a new instance of the Application.

 

[sustorm]

I can do that but then I will have to look at my excel code. I have a form which a user opens an excel sheet only when needed, then they continue to fill out the form and at the end they click and exports the form data into an excel sheet and then copys that sheet into a new file, then attaches it and emails it. The step i am in now is I need to copy the optional sheet if it is open but i am not able to get it to workif they are in different sessions of excel, i hope that made sense

 

[sustorm]

I worked around this by doing the following:

Set apxl = CreateObject("Excel.Application")
    
    
    If ExcelClick = Yes Then
    Set apxl = GetObject(, "Excel.Application")
    Else
    End If

I passed a value when a button is clicked to open the other excel sheet to a text box, if the text box equals yes then change from Create to Get adn it works without a problem. Thanks for your help

 

[ByteMyzer]

Your original code should work if you make the following modification at the beginning of your code:

    Set rst = frm.RecordsetClone
    [B][COLOR="Red"]On Error Resume Next[/COLOR][/B]
    Set Apxl = GetObject(, "Excel.Application")
    If Apxl Is Nothing Then
    Set Apxl = CreateObject("Excel.Application")
    End If
    [B][COLOR="red"]On Error Goto 0[/COLOR][/B]