Can I get Access to automatically name a file export with a variable part of the file name and a fixed part of the filename

Question

I am working on an Access 2014 DB that exports the results of a query to an excel file that can be sent to a third party. I want the resulting excel file to go to a specified folder on a shared drive, with a naming scheme of "serialnumber vendor ASL.xlsx"

Any hints I should look at, pointers to websites that I did not find with Duck Duck Go, all would be greatly appreciated

Right now I am using a simple Macro to export the file with the "ExportWithFormatting" but I cant seem to get anything going in the "Output File" box that gives me any flexibility.

I suspect this can be done with VBS, but I'm pretty shaky when it comes to establishing and running a VBS module. I converted the Macro to a VBS Module, but I have no idea where to go from here.

Option Compare Database

'------------------------------------------------------------
' expord_ASL_to_Excel
'
'------------------------------------------------------------
Function expord_ASL_to_Excel()
On Error GoTo expord_ASL_to_Excel_Err

    DoCmd.OutputTo acOutputQuery, "Match up", "ExcelWorkbook(*.xlsx)", "", True, "", , acExportQualityPrint


expord_ASL_to_Excel_Exit:
    Exit Function

expord_ASL_to_Excel_Err:
    MsgBox Error$
    Resume expord_ASL_to_Excel_Exit

End Function

I tinkered with it a bit, but I can't get it to do anything. I am currently trying to backtrack through the basics of running a VBS module, so I don't need any help with that yet. I'm just trying to figure out this little bit of finesse going.

Answer 1

Per OP's comments, Vendor and Serial are being selected from ComboBoxes on Form.

This code should be placed in the module - overwrite the function that is there. It can be triggered via Macro - RunCode or you can link a button click directly to it.

Just replace the networkPath value with the folder (including the final ) where you want the file to end up.

Then replace the Form("Form1") and ComboBox("SerialComboBox", "VendorComboBox") designations with the names of your form and controls.

Function expord_ASL_to_Excel()
On Error GoTo expord_ASL_to_Excel_Err

Dim networkPath As String

networkPath = "C:\Your\Network\Path\"

exportPath = networkPath & Forms!Form1!SerialComboBox.Value & " " & Forms!Form1!VendorComboBox.Value & " ASL.xlsx"

DoCmd.OutputTo acOutputQuery, "Match up", "ExcelWorkbook(*.xlsx)", exportPath, True, "", , acExportQualityPrint

expord_ASL_to_Excel_Exit:
        Exit Function

expord_ASL_to_Excel_Err:
        MsgBox Error$
        Resume expord_ASL_to_Excel_Exit
End Function