If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Output Query as an Excel file
I am trying to output a query as an excel file and save it to a specific
location. Here is my code. DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _ "C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" & ([Forms]![LCM_DATA]![PER]) & ".xls", False I get a run time error '2306' that states there too many rows to output, based on the limitation specified by the output format or by Microsoft Access. There are 36,464 records. When I take acFormatXLS and the file path and have Access prompt me for the output, I am able to create the file in the folder specified. Not sure what I am doing wrong. Any help will be greatly appreciated! |
#2
|
|||
|
|||
Output Query as an Excel file
acFormatXLS saves the Excel file in an older version, Excel 95 I believe,
that can only handle about 32,000 rows. Look into using TransferSpreadsheet instead. Something like below can handle about 64,000 rows. DoCmd.TransferSpreadsheet acExport, 8, "Cargo_tariff", "c:\temp\Cargo_tariff.xlsx", False, "" If you have Access 2007 changing the 8 to 10 allows about 1 million rows. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "accesshacker" wrote: I am trying to output a query as an excel file and save it to a specific location. Here is my code. DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _ "C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" & ([Forms]![LCM_DATA]![PER]) & ".xls", False I get a run time error '2306' that states there too many rows to output, based on the limitation specified by the output format or by Microsoft Access. There are 36,464 records. When I take acFormatXLS and the file path and have Access prompt me for the output, I am able to create the file in the folder specified. Not sure what I am doing wrong. Any help will be greatly appreciated! |
#3
|
|||
|
|||
Output Query as an Excel file
Hi Jerry,
Thanks for the response, that worked. Also, I did come across another method that works as well, just had to change a little of the code. It is as follows. DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acSpreadsheetTypeExcel9, _ Changed the acFormatXLS to acSpreadsheetTypeExcel9 Thanks again for the quick response! "Jerry Whittle" wrote: acFormatXLS saves the Excel file in an older version, Excel 95 I believe, that can only handle about 32,000 rows. Look into using TransferSpreadsheet instead. Something like below can handle about 64,000 rows. DoCmd.TransferSpreadsheet acExport, 8, "Cargo_tariff", "c:\temp\Cargo_tariff.xlsx", False, "" If you have Access 2007 changing the 8 to 10 allows about 1 million rows. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "accesshacker" wrote: I am trying to output a query as an excel file and save it to a specific location. Here is my code. DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _ "C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" & ([Forms]![LCM_DATA]![PER]) & ".xls", False I get a run time error '2306' that states there too many rows to output, based on the limitation specified by the output format or by Microsoft Access. There are 36,464 records. When I take acFormatXLS and the file path and have Access prompt me for the output, I am able to create the file in the folder specified. Not sure what I am doing wrong. Any help will be greatly appreciated! |
Thread Tools | |
Display Modes | |
|
|