Access only runs once before bugging Runtime error 1004 Method Worksheets of object _ Global Failed

[RCBricker]

As stated in the title.

My db works great the first run, however, it throws the runtime error upon starting a second run unless I go to the task manager and ensure that excel has been quit. I have code to quit excel after saving and closing the workbook that was created by running the reconciliation.


Below is the dim lines used to create GLOBAL variables for excel and the workbook

Public wbIMPORT As Workbook, wbRECON As Workbook
Public appEXCEL As New excel.Application

here is the code that creates the excel instance and the workbook. It later calls a number of subs. the first sub it calls bugs out (see below for further code)

    'STEP_2_4_2
    Set appEXCEL = New excel.Application
    Set wbRECON = appEXCEL.Workbooks.Add
    
    With appEXCEL
        '.Visible = True
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    Select Case strRPT
        Case "GL to TB Data Validation"
            Call STEP_2_4_3_TB2GL_Setup
            Call STEP_2_4_4_Create_Mthly_Sheets
            Call STEP_2_4_5_Fix_GL_DATA
            Call STEP_2_4_6_Create_MTH_CHNG
            Call STEP_2_4_7_Create_TB2DAI_GL_COM

This is the code that bugs. It is in a new procedure. It bugs on the line between the *

    With wbRECON
    'STEP_2_4_3_1
        Set wsCOM = wbRECON.Sheets("Sheet1")
        With wsCOM
            .Name = "TB to " & strSYS & " GL Comparison"
            With wsCOM.Tab
                .Color = 13056
                .TintAndShade = 0
            End With
        End With
    'STEP_2_4_3_2
'*************************************************************************
        Set wsCOA = wbRECON.Worksheets.Add(after:=Worksheets(Worksheets.Count))
'*************************************************************************
        wsCOA.Select

I am sure that there is a simple reason for this, but I cannot for the life of me determine why.

Thanks in advance for any help in this matter.

rich

 

[Norie]

On the line you highlight you aren't specifying the workbook for Worksheets(Worksheets.Count).

 

[RCBricker]

On the line you highlight you aren't specifying the workbook for Worksheets(Worksheets.Count).

Thanks Norie. I knew this was simple just couldn't see it.