Query export to Excel through button (1 Viewer)

[Gasman]

What do you mean by type Pat as they are both xlsx ?

 

[moi]

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

Hello CJ_London,

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.

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

Thank you,

 

[moi]

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.

Sorry pat, i don't get your suggestion..

here is the code for immediate ref.

------
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
-----
Thank you,

 

[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

 

[Pat Hartman]

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.

 

[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?

 

[Pat Hartman]

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

So, you tell Access to export a macro enabled spreadsheet but you name it incorrectly to .xlsx. What do you end up with?

 

[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

 

[Pat Hartman]

If you're ok with the inconsistency of the command, what do I care? My exports use the correct argument so I always know what I am getting.

 

[Gasman]

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

 

[Pat Hartman]

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

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?

 

[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

 

[Pat Hartman]

@moi Try using DoCmd.TransferSpreadsheet rather than OutputTo. Also, when you are naming your file, do NOT use dots to separate the date parts. Many applications cannot read files with names containing multiple dots. Use dash or underscore. Print the file name for us also so we can see what your code produced and if it is valid at all.

 

[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?

 

[Pat Hartman]

@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.