V
virgirl920
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
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