How to export more than 65,000 records from 2007

Topic

New Reply

I’m using the code below to run my queries and export to Excel. I’ve tried several file types but they all bomb out if the query comes back with more than 65,000 records.

Any ideas on how I can fix this?

Private Sub Command0_Click()
Dim qdftemp As QueryDef
Dim a As String
For Each qdftemp In CurrentDb.QueryDefs
a = qdftemp.Name
DoCmd.OutputTo acQuery, a, “Excel Workbook (*.xlsx)”, _
“C:DATA” & qdftemp.Name & “.xlsx”, False, “”
Debug.Print qdftemp.Name
Next
End Sub

Reply | Quote

Replies

Try specifying you’re output type as: acSpreadsheetTypeExcel12

MSDN Reference for help on OutputTo.

Regards:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Why not use DoCmd.TransferSpreadsheet

Reply | Quote

Hate to ask but could you edit my code to use the acSpreadsheetTypeExcel12 and a version with the TransferSpreadsheet methods.

Reply | Quote

Try:
DoCmd.OutputTo acOutputQuery, qdeftemp.name, acSpreadsheetTypeExcel12,”C:DATA” & qdftemp.Name & “.xlsx”

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

It errored out with the message that Object was missing

Reply | Quote

Bill,

Have you tried single stepping through the code to see where it is failing and the value of the variables at that point?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Yes I’ve been stepping through and I’ve gotten it to run by changing the line to
DoCmd.OutputTo acOutputQuery, a, acSpreadsheetTypeExcel12, “C:DATA” & qdftemp.Name & “.xls”
When I try the extension of “xlsx” I can’t open the file.

When I try the query with +65,000 records I still get an error saying it can’t export that many lines. It will save the file but stops at 65,535 lines.
The error message says I’ve selected more records than can be placed on the clipboard.

Reply | Quote

Bill,

.xls files can only have 65,535 rows! To get more than that you need to use the .xslx filetype. Why it won’t work with that filetype I don’t know. What version of Excel are you using?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

FIXED IT!!! This one works perfectly! just had to change some things around and used xlsb extension.

THANK YOU!!!!!

DoCmd.TransferSpreadsheet acQuery, acSpreadsheetTypeExcel12, a, “C:DATA” & qdftemp.Name & “.xlsb”, False, “”

Reply | Quote

Bill,

Here’s an interesting article on the .xlsb file format. I just had to research it after your post. Glad you have it working.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I’m using Excel 2007. When I use the xlsx extension the file will not open. When I rename the exported file to an xls extension it will open.

Reply | Quote