Renaming exported query (1 Viewer)

[hllary]

I have a button that exports a query to an excel. The issue is the name of the query is in the file name. Is there a way the user has to enter a file name?

My code is:

Private Sub BtnExport_Click()

On Error GoTo Err_Handler
    
    DoCmd.Requery (ListboxExport_qry)
    DoCmd.OutputTo acOutputQuery, "ListboxExport_qry", acFormatXLSX, sFilename, True
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "The exported excel was not saved."
    Resume Exit_Handler
        
End Sub

 

[Gasman]

So why are you not supplying a filename in sFileName ?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.outputto

 

[bastanu]

Use FileDialog to allow them to save the file with their choice of name

Dim intChoice As Integer
Dim sFilename As String 'you might already have this declared somewhere else

'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
'determine what choice the user made
If intChoice <> 0 Then
'get the file path selected by the user
sFilename = Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)
DoCmd.OutputTo acOutputQuery, "ListboxExport_qry", acFormatXLSX, sFilename, True

Cheers,
Vlad