Problem while copying data from recordset to excel

I am using below code

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\work\User Audit Tool\AuditReports\;" & _
"Extended Properties=""Text;HDR=No;"""

rs.Open "Select * from AuditReport.csv", cnt, adOpenStatic, adLockReadOnly, adCmdText
Set objXLApp = CreateObject("Excel.Application")
Set xlWb = objXLApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
objXLApp.Visible = True

i = 1
With rs
.MoveFirst
Do Until .EOF
xlWs.Cells(i, 1).CopyFromRecordset rs, 1
i = i + 1
'objWS.Range(objWS.Cells(1, 1), objWS.Cells(1, rs.Fields.Count)).Font.Bold = True


If i > 10 And i < 21 Then
Set xlWs = objXLApp.Worksheets.Add
'objWS.Range(xlWs.Cells(i - 11, 1)).CopyFromRecordset rs
xlWs.Cells(i - 10, 1).CopyFromRecordset rs
End If
If i > 20 Then
Set xlWs = objXLApp.Worksheets.Add
xlWs.Cells(i - 20, 1).CopyFromRecordset rs
End If
.MoveNext
Loop
End With

But when i try to debug using F8 ,the debugger only moves between two statements below .MoveNext statement
ie.
Do Until .EOF
xlWs.Cells(i, 1).CopyFromRecordset rs, 1

But somehow it copies first 10 records to sheet 1 and the rest of them to sheet 2 ...don't know how can it add sheet without the cursor going into the sheet adding statements.

Please help!!

how many records in total?

why have that part of the code to add sheets, if you want all the records in the same sheet?

it would appear that for every record above 10 it should add an additional worksheet to the workbook

Quote:

---

Originally Posted by westconn1

how many records in total?

why have that part of the code to add sheets, if you want all the records in the same sheet?

it would appear that for every record above 10 it should add an additional worksheet to the workbook

---

I want to apply this code if the records exceed 65535 limit of excel sheet.
But right now i am trying it for 130 records or so ,just to make sure they are multiple sheets after each 10 records.
I am not sure if Recordset will be able to hold that many records or not,will it?

So my problem is basically a .csv file with huge amount of data so if records exceed 65356 ,they are pasted on new sheet.

Quote:

---

If i > 10 And i < 21 Then

---

in this every i between 10 and 21 would add a new sheet, when really you only want a new sheet when i = 10 (or really 65535) or multiple thereof

vb Code:

if i mod 10 = 0 then ' add new sheet (or i mod 65535)

Quote:

---

I am not sure if Recordset will be able to hold that many records or not,will it?

---

i am not sure of any limitations in a recordset, that may be a question for the database forum and may depend on the database used, in any case there would be other methods to read from a csv file if necessary

to allow for upgrade to later versions of excel you should use rows.count rather than the literal value of 65535