Programatic approach to splitting Excel exports larger than 65536

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone already have a programmatic approach to splitting the export of
a dataset larger than 65536 records into Excel? I understand the nature of
the limitation, so I'm not looking for a dissertation on why it's a problem.
Rather, I'm looking for code that will place some records (~65K) into each
worksheet/workbook until all the records are transfered. I know that I can
do it, but I was hoping that maybe some kind soul would share their code to
help save me some time. Unfortunately, this has to be exported into Excel
and not text, csv, or anything else.

Thanks,
Chris
 
hi Chris,
Does anyone already have a programmatic approach to splitting the export of
a dataset larger than 65536 records into Excel?
Click to expand...
Air code:

rc = DCount("*", "exportQuery")
For Count = 0 To rc \ 65536
Set rs = OpenRecordset(
"SELECT * FROM exportQuery " & _
"WHERE rowNo " & _
"BETWEEN " & Count * 65536 & _
" AND " & (Count + 1) * 65526)
objExcel.Range.CopyFromRecordset rs
Next Count
I understand the nature of the limitation, so I'm not looking for a dissertation on why it's a problem.
Click to expand...
But why must you use such an "crippled" export format? Why don't you
export XML, which is also readable by Excel?


mfG
--> stefan <--
 
Stefan,

Thanks! I've never used the CopyFromRecordset method before. One problem
I'm having... Jet isn't liking your RowNo. I tried RowNum and that doesn't
work either. Anyone know the solution here?

Thanks,
Chris
 
FYI, this is what I worked out:

Public Function LargeExport(strExportLocation As String, tbl As TableDef, _
db As Database) As Integer

Dim rs As Recordset
Dim fld As Field
Dim wbk As Workbook
Dim intHierarchyFileCount As Integer
Dim lngRecordMin As Long
Dim lngRecordMax As Long
Dim i As Integer
Dim n As Integer

Set fld = tbl.CreateField("RowNum", dbLong)

fld.Attributes = dbAutoIncrField

tbl.Fields.Append fld

tbl.Fields.Refresh

db.TableDefs.Refresh

If Round(tbl.RecordCount / 50000, 0) <> tbl.RecordCount / 50000 Then
intHierarchyFileCount = Int((tbl.RecordCount / 50000)) + 1
Else
intHierarchyFileCount = (tbl.RecordCount / 50000)
End If

For i = 0 To intHierarchyFileCount - 1

lngRecordMin = (i * 50000) + 1
lngRecordMax = (i + 1) * 50000

Set rs = db.OpenRecordset("SELECT * FROM [" & tbl.Name & "]" & vbCr
& _
"WHERE RowNum BETWEEN " & lngRecordMin & " AND " & _
lngRecordMax & ";", dbOpenSnapshot)


If FileExists(strExportLocation & i + 1 & ".xls") Then _
Kill (strExportLocation & i + 1 & ".xls")

Set wbk = myExcel.Workbooks.Add

For n = 0 To rs.Fields.Count - 1

wbk.Sheets(1).Cells(1, n + 1) = rs.Fields(n).Name

Next 'n

wbk.Sheets(1).Cells(2, 1).CopyFromRecordset rs

wbk.Sheets(1).Columns(rs.Fields.Count).Delete

wbk.SaveAs Filename:=(strExportLocation & i + 1)

wbk.Close

Next 'i

rs.Close

tbl.Fields.Delete fld.Name

tbl.Fields.Refresh

LargeExport = intHierarchyFileCount

Set rs = Nothing
Set wbk = Nothing
Set fld = Nothing

End Function
 
Back
Top