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.

Your code indicates Access-which is VBA, but your question indicates VBS. Are you working in VBA? In addition, do you want to hardcode the path of the export or would you prefer a prompt for selecting the file location (potentially with the target filename already prefilled?) — Mike, May 16 '19 at 17:45
I guess I'm using VBA, whatever is a part of Access. I'm fairly good with using all the native stuff in Access, but getting into the guts like this is where I have problems. My end result I hope will be a fixed location followed by a target filename prefilled, but the filename will be based on the value returned in field of the query. I could also use the value in a box on a form. Actually, I suspect the form value might be better — Paul TIKI, May 16 '19 at 17:48
So this is a bit of a multi-area issue. I can point you in the right direction for some good reading. If you supply a bit of info, I can block out some example code for you. Generally, variable items like this would utilize the results of the query itself. For that we need recordsets. See:https://stackoverflow.com/questions/5864160/code-to-loop-through-all-records-in-ms-access . We can get vendor/serial from the recordset and concat w/directory and insert them here `DoCmd.OutputTo acOutputQuery, "Match up", "ExcelWorkbook(*.xlsx)", [FULL PATH], True, "", , acExportQualityPrint` — Mike, May 16 '19 at 17:56
Unfortunately I can't share recordsets because of confidentiality. However, I have a general structure of a front form where a serial number and Vendor are put into 2 separate Unbound combo boxes. The Query uses those two boxes as criteria for the query results, and that is what gets exported, 30 to 200 records at a time. Can we use the values in those combo boxes to concat into the target filename? Your code snippet makes sense, I was trying to put the path in the wrong place in my experiment. Thanks @Mike — Paul TIKI, May 16 '19 at 18:15
You bet. The combo's make it much easier. In the place of [FULL PATH] try something like this `"C:\Your\Network\Path\" &Forms!YourFormName!YourVendorComboBoxName.Value & " " & Forms!YourFormName!YourSerialComboBoxName.Value & " ASL.xlsx"` — Mike, May 16 '19 at 18:56
Do I put that in the VBA module or in the "Output File" box directly in the Macro? Also, can you write this up as an answer so I can upvote and give you credit? — Paul TIKI, May 16 '19 at 19:15

Mike · Accepted Answer · 2019-06-27T12:11:58.037

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

Some suggestion: [Option Explicit](https://stackoverflow.com/q/1139321/9439330) on top of (every) module, declare exportPath, make networkPath a constant (Const instead Dim), replace`Exit Sub`with`Exit Function`at exit-handler, avoid blanks in table/query names (or be forced to use square-btackets), avoid blanks in paths (filename), use underscore because with blanks they might need additional quotation. Use`vbNullString`instead of`""`to avoid accidental insert of a character and avoid underscore in method names like`expord_ASL_to_Excel`, use PascalCase instead`ExpordASLToExcel` — ComputerVersteher, Jun 15 '19 at 08:19

score 1 · Answer 2 · answered Apr 15 '20 at 22:43

This solution is very helpful to me. With little modification and it worked for me. Thank you all. I need help to apply filter to report and then use the filtered value as discipline (disc) value.

Function exportPDF()

On Error GoTo exportPDF_Err

   Dim Report_Names() As String
   Dim MyReport As String
   Dim networkPath As String
   Dim LDate As String
   Dim LYear As Integer
   Dim LMonth As Integer
   Dim LDay As Integer
   Dim Discipline As String

   MyExport = Reports!rptCurrentWK
   LDate = Date
   LYear = Year(LDate)
   LMonth = Month(LDate)
   LDay = Day(MyExport!WEdate)
   Disc = MyExport!Discipline.Value


   networkPath = "C:\Users\MSS\Drive\Timesheets\Access Export\"

   exportPath = networkPath & "Timesheet-" & Disc & "_WE_" & LYear & 
   "-" & LMonth & "-" & LDay & ".PDF"

  DoCmd.OutputTo acOutputReport, "rptCurrentWK", "PDFFormat(*.pdf)", exportPath, 
  False, "", , acExportQualityPrint

   exportPDF_Exit:
    Exit Function

   exportPDF_Err:
    MsgBox Error$
    Resume exportPDF_Exit
   End Function

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

2 Answers2