Query export to Excel through button

[moi]

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

 

[CJ_London]

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)

 

[Gasman]

Walk your code with F8 and breakpoints.
Inspect your variables.

 

[theDBguy]

I think you forgot to add one more "\" to your file path.

 

[Gasman]

I think you forgot to add one more "\" to your file path.

I thought that, but that is the filename?

Although it is going to be called _Paymentas

 

[CJ_London]

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

 

[GPGeorge]

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")

 

[Gasman]

Have you tried it without the periods as date delimiters? Format(Date, "yyyymmdd")

I was able to rename a file with . in a date string with no issues.

 

[GPGeorge]

I was able to rename a file with . in a date string with no issues.

Thanks for the confirmation.

 

[Gasman]

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.

 

[Pat Hartman]

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

 

[moi]

Have you tried it without the periods as date delimiters? Format(Date, "yyyymmdd")

Hi gpgeorge,
I tried your suggestion, but gives me same error..

Any other suggestion sir..

Thank you.

 

[moi]

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.

 

[moi]

I think you forgot to add one more "\" to your file path.

Hi theDBguy,

To where should i insert "\"..

Thank you.

 

[GPGeorge]

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.

 

[Gasman]

This works

DoCmd.OutputTo acOutputQuery, "qry1",acformatxlsx,"F:\temp\qry1.xlsx",,,,acExportQualityPrint

Yours is

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?

 

[theDBguy]

Hi theDBguy,

To where should i insert "\"..

Thank you.

Please ignore what I said. The others already pointed out I probably read it wrong.

 

[CJ_London]

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

 

[Pat Hartman]

Can you please help me, i can't get it work..

You can't get what to work? I can't see your code. How can I help? Did you change the name as I suggested?

 

[Pat Hartman]

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.