How can I export file with variable (in macro)?-CodePudding

I want to export my file in xlsx format. I am able to do this using 'ExportWithFormatting' (see here). However, I would like to name my file by "File-XXXX" with XXXX a value retrieved from my form.

I tried several things which failed like : Picture

Would you have any idea how to do it? Thanks

CodePudding user response：

Hy, it would be best if you program it in VBA. Here is an example how to do it :

Public Function ExportExcelFile(ByVal strFilePath, strFileName As String) As Boolean

    'Example : ? ExportExcelFile("C\MyDocuments\","TestFileName.xlsx")
    
    Dim strQueryName As String
    Dim strFilePathFull As String
    
    Call DoCmd.Hourglass(True)
    
    strFilePathFull = strFilePath & strFileName
    
    strQueryName = "qselPriceListExport"
    
    Call DoCmd.OutputTo(acOutputQuery, strQueryName, acFormatXLSX, strFilePathFull, Autostart:=False)
    
    ExportExcelFile = True
    
    Call DoCmd.Hourglass(False)
    
End Function

To implement this, for example on a form you can go to lets say the button, go to events and select On Click then select the bottom most option, afterwards VBA IDE will open. Here you can paste the code bellow :

Private Sub btnYourButtonNameHere_Click()

     call ExportExcelFile("C\MyDocuments\","TestFileName.xlsx")

End Sub