Export >65,536 Rows to Excel

[ryguy7272]

I am trying to export the results of a query, with more than 65,536 rows, to
an Excel sheet. I found this code on this DG, from a while back. Yesterday
it (sort of) worked for me, but today, after changing a few fields in my
query, it is not working at all. It stops at row 65,536, and this is the
line that errors out:
MinID = rst.Fields("Employee")


Entire Macro:
Option Compare Database

Public MinID As Long

Sub Export_Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
MinID = 0
TotalRec = DCount("[Employee]", "MyQuery")
If TotalRec = 0 Then
MsgBox "No records found", vbExclamation, "Export Aborted"
Exit Sub
End If
SheetCount = TotalRec \ 65536
If TotalRec / 65536 > SheetCount Then
SheetCount = SheetCount + 1
End If
Set db = CurrentDb
For i = 1 To SheetCount
strSQL = "SELECT TOP 65536 * FROM MyQuery"
strSQL = strSQL & " ORDER BY Employee"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQuery", "C:\MyWorkBook.xls", False, "Export" & i
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
MinID = rst.Fields("Employee")
rst.Close
Next
Set rst = Nothing
Set db = Nothing
End Sub

Function GetMinID()
GetMinID = MinID
End Function


My query fields are: Employee, Unit Price, Order Date, Company Name, Item,
and Units.


I spent a while fiddling with it this AM but couldn’t get it working; I am
probably missing something simple, just don’t know what.

If anyone could offer some help I would greatly appreciate it.


Regards,
Ryan---

 

[Stefan Hoffmann]

hi Ryan,

I am trying to export the results of a query, with more than 65,536 rows, to
an Excel sheet.

This is the limit for Excel:

http://office.microsoft.com/en-us/excel/HA101375451033.aspx



mfG
--> stefan <--

 

[BeWyched]

Hi Ryan

You could upgrade to Excel 2007 - maximum number of rows = 1,048,576.

Cheers.

BW

 

[ryguy7272]

Thanks Stefan and BeWyched! I am still using Excel 2002, and I know there is
a way to get Access to output data to the older versions of Excel, and if the
65,536 limit is hit, Access would add a Sheet and continue writing data to
the new Sheet. There is some issue with the code; that is the problem. Any
other ideas?


Thanks,
Ryan---

 

[Douglas J. Steele]

Access will not do what you're describing natively.

You'd have to write code to determine how many records are in the recordset,
and write chunks to different spreadsheets.

 

[David W. Fenton]

Access will not do what you're describing natively.

You'd have to write code to determine how many records are in the
recordset, and write chunks to different spreadsheets.

Or different worksheets within a single spreadsheet.

 

[ryguy7272]

I commented out this line:
'MinID = rst.Fields("Employee")

Now, the code runs, but the results of the exported query are not correct in
Excel. I have 94,028 records in the query, and when I export to Excel I get
65,536 rows in a Sheet named 'Export1' (the VBA code conveniently creates two
sheets on the fly); then I get another 65,536 rows in a second sheet named
'Export2'. When I looked at these two sheets I noticed that the output is
the same in Export1 and Export2. What the heck is causing that???

This the code:
Option Compare Database

Public MinID As Long

Sub Export_Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
MinID = 0
TotalRec = DCount("[Employee]", "MyQuery")
If TotalRec = 0 Then
MsgBox "No records found", vbExclamation, "Export Aborted"
Exit Sub
End If
SheetCount = TotalRec \ 65535
If TotalRec / 65535 > SheetCount Then
SheetCount = SheetCount + 1
End If
Set db = CurrentDb
For I = 1 To SheetCount
strSQL = "SELECT * FROM MyQuery"
strSQL = strSQL & " ORDER BY Employee"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQuery", "C:\MyWorkBook.xls", False, "Export" & I
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
rst.Close
Next
Set rst = Nothing
Set db = Nothing
End Sub

Function GetMinID()
GetMinID = MinID
End Function


I'd appreciate any help with this.

Thanks,
Ryan---

 

[BeWyched]

Hi

As you spin through the 'I to SheetCount' loop you run the same query -
'MyQuery' each time without filtering the next batch of records. Hence you
are ending up with the same truncated content on each sheet. You should
include a filter in MyQuery to pick up each batch in turn. Let me know if you
need help in doing this.

Cheers.

BW

I commented out this line:
'MinID = rst.Fields("Employee")

Now, the code runs, but the results of the exported query are not correct in
Excel. I have 94,028 records in the query, and when I export to Excel I get
65,536 rows in a Sheet named 'Export1' (the VBA code conveniently creates two
sheets on the fly); then I get another 65,536 rows in a second sheet named
'Export2'. When I looked at these two sheets I noticed that the output is
the same in Export1 and Export2. What the heck is causing that???

This the code:
Option Compare Database

Public MinID As Long

Sub Export_Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
MinID = 0
TotalRec = DCount("[Employee]", "MyQuery")
If TotalRec = 0 Then
MsgBox "No records found", vbExclamation, "Export Aborted"
Exit Sub
End If
SheetCount = TotalRec \ 65535
If TotalRec / 65535 > SheetCount Then
SheetCount = SheetCount + 1
End If
Set db = CurrentDb
For I = 1 To SheetCount
strSQL = "SELECT * FROM MyQuery"
strSQL = strSQL & " ORDER BY Employee"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQuery", "C:\MyWorkBook.xls", False, "Export" & I
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
rst.Close
Next
Set rst = Nothing
Set db = Nothing
End Sub

Function GetMinID()
GetMinID = MinID
End Function


I'd appreciate any help with this.

Thanks,
Ryan---

--
RyGuy

Or different worksheets within a single spreadsheet.

 

[ryguy7272]

I'm not sure how to add a filter to MyQuery. It would be great if you could
show me how to do this.

Thanks,
Ryan---


--
RyGuy

Hi

As you spin through the 'I to SheetCount' loop you run the same query -
'MyQuery' each time without filtering the next batch of records. Hence you
are ending up with the same truncated content on each sheet. You should
include a filter in MyQuery to pick up each batch in turn. Let me know if you
need help in doing this.

Cheers.

BW

I commented out this line:
'MinID = rst.Fields("Employee")

Now, the code runs, but the results of the exported query are not correct in
Excel. I have 94,028 records in the query, and when I export to Excel I get
65,536 rows in a Sheet named 'Export1' (the VBA code conveniently creates two
sheets on the fly); then I get another 65,536 rows in a second sheet named
'Export2'. When I looked at these two sheets I noticed that the output is
the same in Export1 and Export2. What the heck is causing that???

This the code:
Option Compare Database

Public MinID As Long

Sub Export_Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
MinID = 0
TotalRec = DCount("[Employee]", "MyQuery")
If TotalRec = 0 Then
MsgBox "No records found", vbExclamation, "Export Aborted"
Exit Sub
End If
SheetCount = TotalRec \ 65535
If TotalRec / 65535 > SheetCount Then
SheetCount = SheetCount + 1
End If
Set db = CurrentDb
For I = 1 To SheetCount
strSQL = "SELECT * FROM MyQuery"
strSQL = strSQL & " ORDER BY Employee"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQuery", "C:\MyWorkBook.xls", False, "Export" & I
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
rst.Close
Next
Set rst = Nothing
Set db = Nothing
End Sub

Function GetMinID()
GetMinID = MinID
End Function


I'd appreciate any help with this.

Thanks,
Ryan---

--
RyGuy

Access will not do what you're describing natively.

You'd have to write code to determine how many records are in the
recordset, and write chunks to different spreadsheets.

Or different worksheets within a single spreadsheet.

 

[BeWyched]

Will do.

Firstly, can you post the SQL beyhind 'MyQuery' - open the query in design
view, choose design view and copy/paste the SQL statement.

I'm not sure how to add a filter to MyQuery. It would be great if you could
show me how to do this.

Thanks,
Ryan---


--
RyGuy

Hi

As you spin through the 'I to SheetCount' loop you run the same query -
'MyQuery' each time without filtering the next batch of records. Hence you
are ending up with the same truncated content on each sheet. You should
include a filter in MyQuery to pick up each batch in turn. Let me know if you
need help in doing this.

Cheers.

BW

I commented out this line:
'MinID = rst.Fields("Employee")

Now, the code runs, but the results of the exported query are not correct in
Excel. I have 94,028 records in the query, and when I export to Excel I get
65,536 rows in a Sheet named 'Export1' (the VBA code conveniently creates two
sheets on the fly); then I get another 65,536 rows in a second sheet named
'Export2'. When I looked at these two sheets I noticed that the output is
the same in Export1 and Export2. What the heck is causing that???

This the code:
Option Compare Database

Public MinID As Long

Sub Export_Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
MinID = 0
TotalRec = DCount("[Employee]", "MyQuery")
If TotalRec = 0 Then
MsgBox "No records found", vbExclamation, "Export Aborted"
Exit Sub
End If
SheetCount = TotalRec \ 65535
If TotalRec / 65535 > SheetCount Then
SheetCount = SheetCount + 1
End If
Set db = CurrentDb
For I = 1 To SheetCount
strSQL = "SELECT * FROM MyQuery"
strSQL = strSQL & " ORDER BY Employee"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQuery", "C:\MyWorkBook.xls", False, "Export" & I
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
rst.Close
Next
Set rst = Nothing
Set db = Nothing
End Sub

Function GetMinID()
GetMinID = MinID
End Function


I'd appreciate any help with this.

Thanks,
Ryan---

--
RyGuy


:


Access will not do what you're describing natively.

You'd have to write code to determine how many records are in the
recordset, and write chunks to different spreadsheets.

Or different worksheets within a single spreadsheet.

 

[ryguy7272]

Please note: this is just a silly query that I am playing with to understand
the VBA. SQL below:
SELECT [Order Details].UnitPrice, Data.Employee, Orders.OrderDate,
Customers.CompanyName, Data.Item, Data.Units
FROM Data, (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY [Order Details].UnitPrice, Data.Employee, Orders.OrderDate,
Customers.CompanyName, Data.Item, Data.Units;

Note also: I have three tables, which are related, and a forth, which is
completely unrelated to the other three; this gives me a Cartesian Product,
which yields 94,028 records. If I didn’t include that forth table (which is
actually nonsense), I would have only 2,137 records and this whole exercise
would be moot. Again, I’m trying to learn the VBA piece. Hope the SQL piece
is helpful; not sure if it is.

Thanks for the help BeWyched!!
Ryan---


--
RyGuy

Will do.

Firstly, can you post the SQL beyhind 'MyQuery' - open the query in design
view, choose design view and copy/paste the SQL statement.

I'm not sure how to add a filter to MyQuery. It would be great if you could
show me how to do this.

Thanks,
Ryan---


--
RyGuy

Hi

As you spin through the 'I to SheetCount' loop you run the same query -
'MyQuery' each time without filtering the next batch of records. Hence you
are ending up with the same truncated content on each sheet. You should
include a filter in MyQuery to pick up each batch in turn. Let me know if you
need help in doing this.

Cheers.

BW

:

I commented out this line:
'MinID = rst.Fields("Employee")

Now, the code runs, but the results of the exported query are not correct in
Excel. I have 94,028 records in the query, and when I export to Excel I get
65,536 rows in a Sheet named 'Export1' (the VBA code conveniently creates two
sheets on the fly); then I get another 65,536 rows in a second sheet named
'Export2'. When I looked at these two sheets I noticed that the output is
the same in Export1 and Export2. What the heck is causing that???

This the code:
Option Compare Database

Public MinID As Long

Sub Export_Query()
Dim db As DAO.Database
Dim rst As DAO.Recordset
MinID = 0
TotalRec = DCount("[Employee]", "MyQuery")
If TotalRec = 0 Then
MsgBox "No records found", vbExclamation, "Export Aborted"
Exit Sub
End If
SheetCount = TotalRec \ 65535
If TotalRec / 65535 > SheetCount Then
SheetCount = SheetCount + 1
End If
Set db = CurrentDb
For I = 1 To SheetCount
strSQL = "SELECT * FROM MyQuery"
strSQL = strSQL & " ORDER BY Employee"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQuery", "C:\MyWorkBook.xls", False, "Export" & I
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
rst.Close
Next
Set rst = Nothing
Set db = Nothing
End Sub

Function GetMinID()
GetMinID = MinID
End Function


I'd appreciate any help with this.

Thanks,
Ryan---

--
RyGuy


:


Access will not do what you're describing natively.

You'd have to write code to determine how many records are in the
recordset, and write chunks to different spreadsheets.

Or different worksheets within a single spreadsheet.