how to dynamically add worksheets to excel via ado and recursive funct 1

[bslintx]

hey all,

i'm trying to loop through an ado recordset on active directory and dumping results to an excel spreadsheet...

i can create and add to the /xls; however, i'm having trouble with the loop(not expecting the results intended)...

i simulated the ado recordset w/ a for next...

here's what i am trying to do:
(note:query is recursive in nature to "walk" the ad tree hence why trying to simulate with 2 loops)

have a spreadsheet named test.xls that has 3 worksheets("tabs") and each work sheet has data added through the loop within "recursive" loop (5 lines)ea

current results: a spreadsheet named test.xls that has 3 worksheets(tabs) named(1,2,3)..wksheet 1 has data...2 and 3 no

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

     oExcel.ActiveWorkbook.SaveAs strExcelPath

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

 

[PHV]

You always write in the same sheet:
for i = 1 to 3 ' recursive tree loop
iExcelRow = 2
Set oSheet = oExcel.ActiveWorkbook.Worksheets(i)
oSheet.Name = i
[!]oSheet[/!].Cells(1, 1) = "Name"
for j = 1 to 5 ' recordset loop
[!]oSheet[/!].Cells(iExcelRow, 1) = j
iExcelRow = iExcelRow + 1
next
next

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

 

[bslintx]

how i missed that i'll never know...kudos phv...was bugging me...much appreciated...and yes...that was the prob...thanks!