Solved

Export Query to Excel 2010

Posted on 2014-11-07
11
232 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 500 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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now