Create XLSX worksheets

[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

 

[vbaInet]

Have a look at this (from boblarson):

http://www.btabdevelopment.com/ts/default.aspx?PageId=49

 

[ino_mart]

To vbaInet

The provided link is an example to open an existing workbook at a predefined worksheet. So both must exist.

I need to create a new workbook and inside that workbook an worksheet for each KPI.

In meantime I did some further investigation and the code stucks when there is no worksheet available anymore.

In my original code I created a workbook with only 1 worksheet (line not in original code included). I changed this so a default Excel file is created with 3 tabs.

As long as intWorkbook <= 3, the code works. It fails as soon it is 4 (as there is no 4th worksheet).

I did some further testing and I found a way to get (nor or less) what I want

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add(1)
Set oSheet = oBook.sheets.Add
oSheet.name=rskpi!kpi
oSheet.Range("A1")= strValue

 

[vbaInet]

Did you explore the other links? There are lots of other examples on there you can pull from, e.g.

http://www.btabdevelopment.com/ts/export2ExcelNewNamedSheet