Query export to Excel through button (1 Viewer)

moi

Member
Joined
Jan 10, 2024
Messages
273
Hello everyone,

I would like to export my query to excel, and I grab the below code, sorry i don't remember the owner..

-------

Code:
Dim filepath As String
    Dim FileName As String
 
    filepath = Environ("userprofile") & "\Desktop\qryCrosstab_Payment"
    FileName = filepath & "as of" & Format(Date, "mm.dd.yy") & ".xlsx"
 
    DoCmd.OutputTo acOutputQuery, "qryCrosstab_Payment", acFormatXLSX, FileName, , , , acExportQualityPrint
-------

But it pops up a Run-time error '2302': Cant save the output data to the file you've selected.

Can someone please examine the code and fix the error..

Thank you
 
Last edited by a moderator:
Nothing wrong with the code so far as I can see. Check your query - does it exceed 65k rows? does it have an error in the data when executed? are the parameters populated? (for Xtabs you must specify the parameters)
 
Walk your code with F8 and breakpoints.
Inspect your variables.
 
I think you forgot to add one more "\" to your file path.
 
think you forgot to add one more "\" to your file path.
no - works in the immediate window

?Environ("userprofile") & "\Desktop\qryCrosstab_Payment" & "as of" & Format(Date, "mm.dd.yy") & ".xlsx"
C:\Users\ChrisA\Desktop\qryCrosstab_Paymentas of05.03.24.xlsx

missing some spaces I suspect to make it readable and personally I wouldn't use . as a date delimiter, but that is up to the OP
 
Hello everyone,

I would like to export my query to excel, and I grab the below code, sorry i don't remember the owner..

-------
Dim filepath As String
Dim FileName As String

filepath = Environ("userprofile") & "\Desktop\qryCrosstab_Payment"
FileName = filepath & "as of" & Format(Date, "mm.dd.yy") & ".xlsx"

DoCmd.OutputTo acOutputQuery, "qryCrosstab_Payment", acFormatXLSX, FileName, , , , acExportQualityPrint
-------

But it pops up a Run-time error '2302': Cant save the output data to the file you've selected.

Can someone please examine the code and fix the error..

Thank you
Have you tried it without the periods as date delimiters? Format(Date, "yyyymmdd")
 
Thanks for the confirmation.
I have in the past renamed files that way.
Let's say I want to email an exe file to myself when at another place, I would add .txt to the end of the filename, so that it would not be blocked, then remove it when received.
 
acFormatXLSX,
This is the macro format.
acSpreadsheetTypeExcel12Xml,
This is the standard format. The developers who made these names were sloppy. They should have named the standard export
acSpreadsheetTypeExcel12 and the macro
acSpreadsheetTypeExcel12Macro
 
H
This is the macro format.

This is the standard format. The developers who made these names were sloppy. They should have named the standard export
acSpreadsheetTypeExcel12 and the macro
acSpreadsheetTypeExcel12Macro
I pat,

Can you please help me, i can't get it work..
Thank you.
 
Hi gpgeorge,
I tried your suggestion, but gives me same error..

Any other suggestion sir..

Thank you.
Your output command has a number of empty commas. Please verify that you have actually included the right arguments in the right places, and that all of the commas are required. It's easy to miss one out or add an extra one.
 
  • Like
Reactions: moi
This works
Code:
DoCmd.OutputTo acOutputQuery, "qry1",acformatxlsx,"F:\temp\qry1.xlsx",,,,acExportQualityPrint

Yours is
Code:
DoCmd.OutputTo acOutputQuery, "qryCrosstab_Payment", acFormatXLSX, FileName, , , , acExportQualityPrint

I cannot see any difference?
Debug.Print FileName and report back here.
Can you change the folder to somewhere else? Hardcode a filename and path just as a test.
Can you actually write to your desktop?
 
As I stated in post #2 the is nothing wrong with the code so the issue has to be with the query. Either misspelled or requires parameters (name implies it is an xtab so any parameters must be declared)

we are now 17 posts in and the OP would appear to be ignoring this simple check
 
As I stated in post #2 the is nothing wrong with the code
Except that the extension doesn't match the export type. The .xlsx extension is the XML format but the type is the macro format. That seems to jibe with the error message.
 

Users who are viewing this thread

Back
Top Bottom