export query results at the push of a button

[Guest]

Hi. I have been trying to save my query results as an Excel document at the
push of a command button, but it is not working. I also want the user to be
able to enter the file name to save the results as.

I have tried doing this using a Macro, using VB (both using
TransferSpreadsheet), and using the code below by Dylan Moran, all of which
do not work.

When using the transferSpreadsheet command, I am not sure how to use the
FilePath part, since I had the user enter a new spreadsheet to create. How
would I do this, if the user enters the name in a textbox? It keeps sayng it
is an invalid path. This is the VB code that I have tried to use:

fileName = Me!txtFileName.Text

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5,
"qryDealerInfo", ["Desktop\" + fileName(*.xls)], True

The brackets are very wrong and result in many errors, but that's what I
want the file saved to be. That's just the latest way I've tried.


In Dylan Moran's code, the ahtAddFilterItem is getting an error...do I have
to import some files, or have additional code so it recognizes what aht...is?


I would appreciate any solution that lets me do this. I am completely new
to Access (only learned it this past week).


Dim strFilter As String
Dim strSaveFileName As String

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "EnterQueryNameHere", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If

 

[Guest]

I've resolved this, sorry. If I could figure out how to delete posts I would
delete this one!