Create XLSX with different worksheets based upon table value

[ino_mart]

All

I am trying to create an Excel 2007 file with multiple worksheets via VBA-code in Access 2007. The name of a worksheet is based upon a value in a table. With code below, I get error message "run-time error 9: subscript out of range".

The first time the code goes into the while-block, it succeeds. But it fails the second time on line in red (see below). I expected this line would set focus to the worksheet I just created (but seems I got it wrong).

Private Sub CreateXLS
'create Excel object
Set oExcel = CreateObject("Excel.Application")
strFileName = "report.xlsx"
strSQL = "select distinct KPI from dbo.tblKPI"
Set rsKPI = New ADODB.Recordset
rsKPI.Open strSQL, cnCap, adOpenDynamic, adLockOptimistic
rsKPI.MoveFirst
 
IntHeaderRow = 6
intCounter = intHeaderRow + 1
intWorksheet = 1
While rsKPI.BOF = False And rsKPI.EOF = False
        Set oBook = oExcel.Workbooks.Add(intWorksheet)
        [COLOR=red]Set oSheet = oBook.Worksheets(intWorksheet)[/COLOR]
        oSheet.Name = rsKPI!kpi
 
        'Define titles in Headerrow
        oSheet.Range("A" & intHeaderRow).value = "Country: "
        '... publish some other titles
        strSQL = "select * from tblData where KPI='" & rsKPI!KPI & "'"
        set rsData = New ADODB.Recordset
        rsData.Open strSQL, cnCap, adOpenDynamic, adLockOptimistic
        while rsData.BOF = False and rsData.EOF = False
            '...export to Excel
             oSheet.Range("A" & intCounter).value = rsData!Country
             '... publish other fields of recordset rsData
             rsData.MoveNext
             intCounter = intCounter + 1
        wend
        intWorksheet = intWorksheet + 1
        intCounter = intHeaderRow + 1
        rsKPI.MoveNext
Wend
'Save file
oBook.SaveAs strFileName
oExcel.Quit
end sub

 

[xenou]

        Set oBook = oExcel.Workbooks.Add([COLOR="Red"]intWorksheet[/COLOR])
        Set oSheet = oBook.Worksheets(intWorksheet)

try instead without a parameter in the add method:

        Set oBook = oExcel.Workbooks.Add
        Set oSheet = oBook.Worksheets(intWorksheet)

I'm not sure what you're trying to do with this intWorksheet variable actually ...