Export to Excel

[KabirPatel]

Hi,

I have an .adp that executes a stored procedure. The results of this stored procedure exceed 65535 records. I put the output of this stored proc into an ADO recordset and want to export into Excel, however it will no let me as the maximum allowed in Excel is 65535 rows.

Is there an easy way for me to break this up into chunks of 65535 so that I can export it to Excel?

Thanks
Kabir

 

[Moonshine]

I think this could be done with a For statement, ive no chance of writing the code cos its too early but something like counting the records to get the total, then doing a for each with maybe a do loop until you get to say 65000, put that in one spreadsheet/temp table, then for the next lot of 65001 to 125000 and so on...

Might be a bit tricky with the For and Loop, but theoretically it should work!

 

[stuartam]

this should work:

row = 2
With rs
    .Open batchsql, cn, adOpenStatic, adLockOptimistic, adCmdText
    If Not rs.EOF Then
        With objexcel
            .Visible = False
            .Workbooks.Open link.Templates & "batchRM2ngs.xls"
            Do Until rs.EOF
                row2 = row
                If Not rs.EOF Then
                    Select Case row
                        Case Is < 65000
                            objexcel.Worksheets("page1").Activate
                            objexcel.Range("A" & row).Activate
                            objexcel.ActiveCell.Value = rs!ref
                        Case 65000 To 130000
                            row2 = row2 - 65000
                            objexcel.Worksheets("page2").Activate
                            objexcel.Range("A" & row2).Activate
                            objexcel.ActiveCell.Value = rs!ref
                    End Select
                End If
                row = row + 1
                rs.MoveNext
            Loop
        End With
    End If
End With

 

[KabirPatel]

Thanks for that.

I ended up using the following:

=========================================
While Not rs.EOF

Row= 1
Col = 1

'Increment the row counter
Row= Row+ 1

If rs.RecordCount > 60000 Then
objWSheet.Cells(intRow, Col).CopyFromRecordset rs, 60000
Else
objWSheet.Cells(intRow, Col).CopyFromRecordset rs, rs.RecordCount
End If

rs.MoveFirst

If rs.RecordCount >= 60000 Then
Set objWSheet = Nothing
intSheet = intSheet + 1
objWBook.Sheets.Add After:=objWBook.Sheets(objWBook.Sheets.Count)
Set objWSheet = objWBook.Worksheets(intSheet)
End If

'Delete all the rows that have already been exported
For intX = 1 To 60000
rs.Delete 1
rs.MoveFirst
If rs.EOF = True Then
Exit For
End If
Next
Wend
========================================

Its a bit long winded but I think CopyFromRecordset is faster than copying rows one at a time.

The only thing that seemed to annoy me was the deletion of records from the recordset. Surely there must be an easier way to mass delete from an ADO recordset - how I miss ADO.NET....

The whole thing sometimes times out aswell, especially when the volume of data exceeds 100000.

Cheers,
Kabir