Prompt SaveAs dialog when exporting data

[Snowflake68]

I have found this code on the web that will prompt the SaveAs dialog box when exporting a file but how can I modify it so that it populates the filename with a specific filename such as "Results" plus the current date?

Public Function saveFileAs() As String

Dim fd As FileDialog, fileName As String

    On Error GoTo ErrorHandler

    Set fd = Application.FileDialog(msoFileDialogSaveAs)
 
    If fd.Show = True Then
        If fd.SelectedItems(1) <> vbNullString Then
            fileName = fd.SelectedItems(1)
        End If
    Else
        'Stop Code Execution for Null File String
        End
    End If
    saveFileAs = fileName

    'Cleanup
    Set fd = Nothing
    Exit Function
 
ErrorHandler:
    Set fd = Nothing
    MsgBox "Error " & Err & ": " & Error(Err)

End Function

I am using the above code in the transfer spreadsheet code below.

Private Sub cmdExcelExport_Click()
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryData", saveFileAs, True

End Sub

 

[arnelgp]

add that to FileDialog InitialFileName, eg:

Set fd = Application.FileDialog(msoFileDialogSaveAs)
fd.InitialFileName = "Results_" & Date

 

[Minty]

Adding the date as it is will cause an error due to the / present in the date format. You'll need to format it something like

Set fd = Application.FileDialog(msoFileDialogSaveAs)
fd.InitialFileName = "Results_" & Format(Date,"yyyymmdd")

 

[Snowflake68]

add that to FileDialog InitialFileName, eg:

Set fd = Application.FileDialog(msoFileDialogSaveAs)
fd.InitialFileName = "Results_" & Date

Works perfectly thank you so much.

 

[Snowflake68]

had to format the date though as wouldnt allow slashes in file names but all good now.

fd.InitialFileName = "SupplyChainMonitor_" & Format(Date, "yyyymmdd")

thanks again.

 

[Snowflake68]

Adding the date as it is will cause an error due to the / present in the date format. You'll need to format it something like

Set fd = Application.FileDialog(msoFileDialogSaveAs)
fd.InitialFileName = "Results_" & Format(Date,"yyyymmdd")

Thanks I discovered that too and managed to sort it

 

[arnelgp]

The excel file, that is what we are putting the cf