Export Access 2013 query to to excel

I would like to export an Access 2013 query to Excel with VBA and ask for file location.
skull52Asked:
Who is Participating?
 
PatHartmanCommented:
This is an Access gotcha!  You would think that acSpreadsheetTypeExcel12 would point to the "normal" spreadsheet format but it doesn't!!!  It points to .xlsb or maybe .xlsm. Who knows.  The option that points to the "normal" spreadsheet format (.xlsx) is the one with the "unnormal" name - acSpreadsheetTypeExcel12Xml.  Now that makes sense???
1
 
Dale FyeCommented:
Have you tried the transferspreadsheet mmethod?

Sent from my iPhone
0
 
skull52Author Commented:
No... will that ask where to save it?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
It will, or will allow to enter a default file name.
0
 
PatHartmanCommented:
Here's a sample from one of my apps.
Me.txtPath is a text box on the form that is running the export.  the code adds the file name in the format
ClientList20150315.xls

    sFileName = Me.txtPath & "\ClientList_" & Format(Date, "yyyymmdd") & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempQD", sFileName
0
 
skull52Author Commented:
Pat,
I like being able to add a file name and the current date to the to the output, i assume that  TempQD would be the query that would generate the output. I also assume that  sFileName  would be DIM as String
0
 
skull52Author Commented:
also I am using excel 2010 and 2013 would the acSpreadsheetTypeExcel9 still be 9 or should it be 12 or 14
0
 
skull52Author Commented:
OK... I am using the following code
Private Sub Command357_Click()
Dim strPath As String

 strPath = "X:\ATI\Stocking Dealer\SDP 2015\STOCKING_DEALER_" & Format(Date, "yyyymmdd") & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryDealers", strPath
End Sub

Open in new window

to export to excel it exports fine but when trying to open it, it is giving me the following error "excel cannot open the file .xlsx because the file format or file extension is not valid"
0
 
skull52Author Commented:
Oh yeah, that make perfect sense why wouldn't you think that XML would translate into xlsx :) Thanks Pat that worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.