Export Query to Excel 2010

Try

DoCmd.TransferSpreadsheet acExport, 10, "Queryname", "C:\PathAndFileName.xlsx"

ExportTable prompts for a filename and save location before exporting.

I'd like to have the same functionality of: one-click to initiate the export and prompt for a file name and save location, just need it to save as .xlsx instead of .xls

If a one-click method for opening a Save as... .xlsx dialog box does not exist, I am willing to implement another solution.

Any suggestions?

This is the code for the ExportQuery function (embedded in a module) I am currently using:

Sub ExportTable(TableName As String)
Dim FileName As String
FileName = ChooseXLFile
If FileName <> "" Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, TableName, FileName, True
End If
End Sub

Select allOpen in new window

I tried changing "acSpreadsheetTypeExcel8" to "10" as suggested above, but the file still exports as .xls rather than .xlsx

What does "ChooseXLFile" contain?

If all you can do (for example) is select .xls files, then it will be saved with an .xls extension, but not necessarily in .xls format (it could be saved in .xlsx format with a .xls extension).

This is "ChooseXLFile":

Function ChooseXLFile() As Variant

Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
Dim varDirectory As String, varTitleForDialog As String
' Don't change directories when done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.

lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR

varDirectory = ""

varTitleForDialog = "Please specify a file to save (*.xls)"

' Define the filter string and allocate space in the "c"
' string.

strFilter = ahtAddFilterItem(strFilter, "Excel (*.xls)", "*.xls")

' Now actually call to get the file name.

varFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
ChooseXLFile = varFileName
End Function

Select allOpen in new window

This is old code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

It now prompts to Save As... an .xlsx file. However, when I attempt to open this file in Excel I get the error: "Excel cannot open the file 'master.xlsx' because the file format or file extension is not valid".

Is there another set of code I can use to export a query to .xlsx with a user supplied filename?

Alternatively, instead of 10, use acSpreadsheetTypeExcel14

Ah, I had reverted back to "acSpreadsheetTypeExcel8" after "10" did not work out.

I have now implemented both of your suggested solutions and it works. Thanks for the tips.