Query export to Excel through button

[Gasman]

Upload your dB with enough to show the issue, plus instruction on how to recreate.

 

[moi]

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?

DoCmd.OutputTo acOutputQuery, "qrypayment", acFormatXLSX, "c:\users\james\onedrive\desktop\qrypayment.xlsx", , , , acExportQualityPrint

I tried the above but same error pops up.

 

[Gasman]

Upload the dB then

 

[CJ_London]

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.

I tried the exact code with a query of my own - and it worked without a problem.

My appologies sir.. and i am not ignoring the suggested check.. but some i can't do it as i don't know how to do it.

you just did it

the query has no parameters, it is opening and shows the records..

all you didn't say is how many rows it produces

At least provide the sql to your query

 

[Gasman]

What part of

acFormatXLSX is the name for the MACRO format and acSpreadsheetTypeExcel12Xml is the name for the STANDARD format did you not understand? Please just try replacing the correct name for what you have.

I thought macros were .xlsm ?
I tried that code and it created an xlsx file without any issues, using the code I posted?

 

[Gasman]

I ended up with an xlsx file?

 

[CJ_London]

@Pat Hartman - perhaps try it for your self - numerous posters on this thread have done so without issue

 

[Gasman]

Macro enabled workbooks are .xls, .xlm, xlb, xlsm or xlsb AFAIK?

 

[CJ_London]

The OP said to create a macro workbook but he gave it the wrong extensio

I don’t see where the OP has said that and I see no code to indicate they are adding macros to the newly created file.

I don’t disagree with using the correct terms but in this case it is irrelevant in that either term creates a file, distracting from trying to find a solution to a file not being created.

research the error number/message

 

[Gasman]

I guess I wasn't being clear. I'll try again. There are two argument values for the TransferSpreadsheet for version 12. One to create a macro workbook (acSpreadsheetTypeExcel12) and the other to create a normal workbook (acSpreadsheetTypeExcel12Xml). The OP was using the argument to create the macro workbook but was naming the workbook with the normal extension.

CJ said it doesn't matter but the two types of workbooks have different formats so the question becomes, what do you end up with? The OP said to create a macro workbook but he gave it the wrong extension so does the file end up with the binary format or the XML format? Is the workbook going to be blocked by some other application if the internal format is binary rather than XML even though the extension is .xlsx?

@Pat Hartman.

You have me intrigued now, so I thought I would do a few tests. However nowhere could I see where the O/P was requiring a amacro enabled workbook?

acformatxlsx

This produces a file which looks like

acSpreadsheetTypeExcel12

acSpreadsheetTypeExcel12Xml

I do not have that available for some reason? I am now on 2019?

I get the same for Excel9 ?

https://learn.microsoft.com/en-us/office/vba/api/access.acspreadsheettype

@moi
I run your code as is, with my query name and that worked fine, except for the lack of spaces in the name, so you have something else going on.
I have asked you to try another folder. Try something simple lke C:\Temp

 

[Gasman]

@moi
We have all been concentrating on why the file is not created, but what do you expect from acExportQualityPrint with a query?

 

[moi]

@Gasman Apparently the OutputTo has different arguments than TransferSpreadsheet does and so cannot create the other format spreadsheet.

Moi has language issues and so has trouble following directions and that makes him very difficult to help. Perhaps he will understand my last suggestion and get the dots out of the file name and try that. Or, try using the TransferSpreadsheet and perhaps get a different error message which will help to identify the problem.

Dear all,

Thank you all for sharing your time helping me..

I sorted thank you.

 

[cheekybuddha]

I sorted thank you

Please share your final solution. It will help other users here who may have the same problem.

 

[Gasman]

We are over 40 posts in this thread , so as cheekybuddha asks, please post the solution. You are here to give as well as take. That is the premise for the forums.