Here is some VBA code. I don't use macros. It gives you the option of having two text boxes. One for the file name and the other to supply the path of where you want the file to go.
Code:
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim reportName As String
Dim theFilePath As String
reportName = me.txtboxname.value
theFilePath = Me.txtfilepath.Value
theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True
MsgBox "Look in your destination folder for the file"
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
It adds the date to the file name and also it was set up for Excel 2002, so you will have to change some code if you want it to be for later versions.