Export Query to Excel 2010

This code exports a query to an .xls file.

Private Sub MasterExport_Click()

Call ExportTable("MasterQuery")

End Sub

Open in new window

How can I export this query as an .xlsx file?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:

DoCmd.TransferSpreadsheet acExport, 10, "Queryname", "C:\PathAndFileName.xlsx"
dougf1rAuthor Commented:
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
dougf1rAuthor Commented:
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dougf1rAuthor Commented:
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

Open in new window

I tried changing "acSpreadsheetTypeExcel8" to "10" as suggested above, but the file still exports as .xls rather than .xlsx
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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).
dougf1rAuthor Commented:
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.


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, _
If Not IsNull(varFileName) Then
varFileName = TrimNull(varFileName)
End If
ChooseXLFile = varFileName
End Function

Open in new window

This is old code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Sadly, the above code was written before Office 2007 came out.

Change the references in rows 15 and 20 from "*.xls" to "*.xlsx", and see what happens.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougf1rAuthor Commented:
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?
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
And are you using DoCmd.TransferSpreadsheet acExport, 10?

And are you using Access 2010.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Alternatively, instead of 10, use acSpreadsheetTypeExcel14
dougf1rAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.