Solved How to include the data, in a specific query field, as part of the file export name (1 Viewer)

[LopeGF]

Hello and good day:

I have been working on this for several days, and just can't figure out, let me explain.

1. I have a query called "Open Orders By Supplier", that is controled by a combo box.
2. Depending on the value in the comobo box, when I run my query, I get the open orders for the "specific" supplier.
   1. For example purposes I have 2 suppliers.
      1. ACME
      2. Oscorp
3. I then Click a button and export this query to Excell, and generate a name to file based on the date and time of the export, I am using the code bellow:
   1. Private Sub Command99_Click()
   2. Dim FilePath As String
   3. Dim FileName As String
   4. FilePath = Environ("userprofile") & "\Documents\18_Open_Orders\Open_Order_Sup_analysis"
   5. FileName = FilePath & "_" & Format(Now, "mm.dd.yyyy_hh.mm") & ".XLSX"
   6. DoCmd.OutputTo acOutputQuery, "qry_Open_Orders_By_Sup_anaysis", acFormatXLSX, FileName, , , , acExportQualityPrint
   7. MsgBox "Exported Successfully", vbInformation, "To Excel"
4. The result i get is a file named:
   1. Open_Order_Sup_analysis_12.12.2022_19.02
      1. Which is EXCELENT!!!
   2. However it would ideal if the name were:
      1. Open_Order_Sup_analysis_ACME_12.12.2022_19.02 or Open_Order_Sup_analysis_OSCORP_12.12.2022_19.02
   3. Depending on what supplier is the one on the query.

Any Help?

 

[arnelgp]

just add the value of your combobox to your FileName:

...
...
FilePath = Environ("userprofile") & "\Documents\18_Open_Orders\Open_Order_Sup_analysis"
FileName = FilePath & "_" & [yourComboboxNameHere] & "_" & Format(Now, "mm.dd.yyyy_hh.mm") & ".XLSX"

 

[June7]

Just more concatenation.

FileName = FilePath & "_" & Me.comboboxname & "_" & Format(Now, "mm.dd.yyyy_hh.mm") & ".XLSX"

That assumes combobox value is the text "ACME" or "Oscorp". If the combobox value is actually an ID then need to reference column that holds the descriptive text. Reference column by its index, index begins with 0: Me.comboboxname.Column(1).

I suggest format date as yyyy.mm.dd_hh.nn to get correct chronological order in file list.

 

[LopeGF]

just add the value of your combobox to your FileName:

...
...
FilePath = Environ("userprofile") & "\Documents\18_Open_Orders\Open_Order_Sup_analysis"
FileName = FilePath & "_" & [yourComboboxNameHere] & "_" & Format(Now, "mm.dd.yyyy_hh.mm") & ".XLSX"

Thank you for your help, this worked out just as I needed.

 

[LopeGF]

Just more concatenation.

FileName = FilePath & "_" & Me.comboboxname & "_" & Format(Now, "mm.dd.yyyy_hh.mm") & ".XLSX"

That assumes combobox value is the text "ACME" or "Oscorp". If the combobox value is actually an ID then need to reference column that holds the descriptive text. Reference column by its index, index begins with 0: Me.comboboxname.Column(1).

I suggest format date as yyyy.mm.dd_hh.nn to get correct chronological order in file list.

Thank for the "concatenation" suggested as well as the date format, it was very helpful.

 

[Pat Hartman]

Having multiple periods in a file name is a recipe for problems. There are places in Access where the multiple dots will cause a failure. It is plain poor practice for Windows.