Add new Sheet

[mkjp2011]

Hello, I am trying to add a new sheet to an active workbook. Below is the code that I am trying to use, but I keep getting a error that reads invalid object.

Set excelReport = CreateObject("Excel.Application")
excelReport.Workbooks.Add

Set excelSheet1 = excelReport.ActiveWorkbook.WorkSheets(1) 'Account Level results
Set busAcctLevelSheet = excelReport.ActiveWorkbook.WorkSheets(2)
Set excelSheet2 = excelReport.ActiveWorkbook.WorkSheets(3)
excelReport.ActiveWorkbook.Add
Set excelSheet3 = excelReport.ActiveWorkbook.WorkSheets(4) 'Not Found Sheet

 

[XavierMarechal]

When I record a simple macro in Excel with just the action of creating a sheet the code is :
Sheets.Add After:=Sheets(Sheets.Count)

So I guess in Attachmate the code would be something like :
excelReport.ActiveWorkbook.Sheets.Add

 

[vzachin]

    Dim excelReport as Object
    Set excelReport = CreateObject("Excel.Application")   
    excelReport.Visible = True
    
    Set excelReport = excelReport.Workbooks.Add
    
    Set excelSheet1 = excelReport.WorkSheets(1) 'Account Level results      
    Set busAcctLevelSheet = excelReport.WorkSheets(2)        
    Set excelSheet2 = excelReport.WorkSheets(3)  
      
    excelReport.Sheets.Add          
    Set excelSheet3 = excelReport.WorkSheets(4)

 

[SkipVought]

    Set excelReport = CreateObject("Excel.Application")
    
    With excelReport.Workbooks.Add
        Set excelSheet1 = .Worksheets(1) 'Account Level results
        Set busAcctLevelSheet = .Worksheets(2)
        Set excelSheet2 = .Worksheets(3)
        Set excelSheet3 = .Worksheets.Add(After:=(.Worksheets.Count))
    '......
        .SaveAs SomePathAndName
        .Close
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]