Exporting and saving file as [FORM]![FORM1]![FILENAME]

[Elbows]

Is there anyway of writing a Macro that exports the results of a query and saves the file as an xls file but the file name is based on what is written in a box on a form (or alternatively the date would be just as useful)?



ie

OpenQuery
ExportData to folder
call the file (Now).xls

[alansidman]

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.

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.