Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Export Query to Excel 2010

Posted on 2014-11-07
11
Medium Priority
?
251 Views
Last Modified: 2014-11-10
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?
0
Comment
Question by:dougf1r
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40429299
Try

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

Author Comment

by:dougf1r
ID: 40429304
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
0
 
LVL 1

Author Comment

by:dougf1r
ID: 40430305
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?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:dougf1r
ID: 40431600
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432240
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).
0
 
LVL 1

Author Comment

by:dougf1r
ID: 40432902
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
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40432909
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.
0
 
LVL 1

Author Comment

by:dougf1r
ID: 40432938
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?
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 2000 total points
ID: 40432956
And are you using DoCmd.TransferSpreadsheet acExport, 10?

And are you using Access 2010.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432961
Alternatively, instead of 10, use acSpreadsheetTypeExcel14
0
 
LVL 1

Author Comment

by:dougf1r
ID: 40433038
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.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question