Link to home
Create AccountLog in
Avatar of dougf1r
dougf1r

asked on

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?
Avatar of Phillip Burton
Phillip Burton

Try

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

ASKER

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
Avatar of dougf1r

ASKER

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?
Avatar of dougf1r

ASKER

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
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).
Avatar of dougf1r

ASKER

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

Open in new window


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

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dougf1r

ASKER

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?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Alternatively, instead of 10, use acSpreadsheetTypeExcel14
Avatar of dougf1r

ASKER

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.