Subscript out of range (9)

[msche398]

I have an Access 2007 database that, when a certain button is clicked, opens excel, and inserts worksheets and data. It works for the most part except for one function. When I run this function, I get the error "Subscript out of range (9)" and it stops. Below is the code.


Function ExportExcel_lot()
Dim xlworkbook As Excel.Workbook
Dim currpath As String
DoCmd.SetWarnings False
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
Set xlworkbook = xlapp.Workbooks.Add

Dim r1 As ADODB.Recordset
Set r1 = New ADODB.Recordset
r1.CursorLocation = adUseClient
xlapp.Sheets.Add
With xlworkbook
.Worksheets(1).Name = "Doff"
.Worksheets(2).Name = "Creel"
.Worksheets(3).Name = "Sizing"


.Worksheets(4).Name = "Packing"
End With
DoCmd.SetWarnings True
End Function

The point where it stops is the .Worksheets(4).Name = "Packing". If I take out this line, the entire function works fine and I have an Excel 2003 format workbook (but using Excel 2007), with 3 worksheets, complete with data and formatting. But if I try to create a 4th worksheet, I get the error again and I only have 3 worksheets.

Does anyone have any ideas why it is doing this? I don't understand why I could have 3 worksheets but not 4. I really appreciate the help.

[boblarson]

You can't assume that there will be enough worksheets in the Excel workbook when you add one. It goes off of the USER'S option settings so if you have it set to only have two worksheets then it will only add two worksheets. You can modify your code to be like this:

 
Function ExportExcel_lot()
    Dim xlworkbook As Excel.Workbook
    Dim currpath As String
    DoCmd.SetWarnings False
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlworkbook = xlApp.Workbooks.Add
    Dim r1     As ADODB.Recordset
    Set r1 = New ADODB.Recordset
    r1.CursorLocation = adUseClient
    With xlworkbook
       Do Until .Worksheets.Count >= 4
           .Worksheets.Add
       Loop
        .Worksheets(1).Name = "Doff"
        .Worksheets(2).Name = "Creel"
        .Worksheets(3).Name = "Sizing"
        .Worksheets(4).Name = "Packing"
    End With
    DoCmd.SetWarnings True
End Function

And you incorrectly had the code
xlapp.Sheets.Add
which is not right. It is the workbook object which should be adding them.