dump ado data to excel xls with subscript error

[bslintx]

hey all,

i though i had the code wrapped up but...

the problem at hand is for whatever reason i get a subscript out of range error when trying to create more than 3 worksheets...

the following simulation of my code...

Function DumpToExcel()
   strExcelPath = "test.xls"
   Set oExcel = CreateObject("Excel.Application")
      oExcel.Visible = True
      
        
   oExcel.Workbooks.Add
   
   
   for i = 1 to 3
     iExcelRow = 1
     Set oSheet = oExcel.ActiveWorkbook.Worksheets(i)
     oSheet.Name = i     
     oSheet.Cells(1, 1) = "Name"     
     for j = 1 to 5 
       oSheet.Cells(iExcelRow, 1) =  j      
       iExcelRow = iExcelRow + 1  
     next   
   next 

   oExcel.ActiveWorkbook.SaveAs strExcelPath
   oExcel.ActiveWorkbook.Close
   oExcel.Application.Quit
End Function

DumpToExcel

however if i changed the first for loop to i = 1 to 4+..i get subscript error...i don't understand this and it would be a tremendous help if someone could test and help w/ a resolution...i didn't think there was a "limitaion" on "how many" worksheets...i hope i'm not overlooking the obvious like last time...perhaps another set of eyes can see if logic is off...thanks

 

[bslintx]

hmmm...guess i should do my homework...resloution seems to be...

Function DumpToExcel()
   strExcelPath = "test.xls"
   Set oExcel = CreateObject("Excel.Application")
      oExcel.Visible = True
      
        
   oExcel.Workbooks.Add
   
   
   for i = 1 to 4 
     iExcelRow = 1
     Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
     Set oSheet = oExcel.ActiveWorkbook.Worksheets.add
     oSheet.Name = i     
     oSheet.Cells(1, 1) = "Name"     
     for j = 1 to 5 
       oSheet.Cells(iExcelRow, 1) =  j      
       iExcelRow = iExcelRow + 1  
     next   
   next 

   oExcel.ActiveWorkbook.SaveAs strExcelPath
   oExcel.ActiveWorkbook.Close
   oExcel.Application.Quit
End Function

DumpToExcel

 

[PHV]

What about this ?

   for i = 1 to 4 
     iExcelRow = 1
[!]     If i <= oExcel.ActiveWorkbook.Sheets.Count Then[/!]
       Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
[!]     Else[/!]
       Set oSheet = oExcel.ActiveWorkbook.Worksheets.add
[!]     End If[/!]
     oSheet.Name = i     
     oSheet.Cells(1, 1) = "Name"     
     for j = 1 to 5 
       oSheet.Cells(iExcelRow, 1) =  j      
       iExcelRow = iExcelRow + 1  
     next   
   next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886