Search code examples
sqlexportms-access-2010period

Export Table out of Access Database to Dynamic Folder Directory

I have a table in my Access data base that I would like to export to a folder directory which is updated manually according to the following methodology (YYYY is the higher-level folder, YYYYMM is the sub-folder I would like to export the report to):

2021:

202101

(...)

202112

2022

202201

(...)

So far, I have the below piece of code to add to a button in my form. One way to avoid pressing it in a later month and then having it copied to the wrong subfolder could be to go via the database name itself (Currentdb.name) as this will be named exactly in line (202112 etc.). Using the below, the report is placed in the same folder as the high-level years folder (among 2021, 2022).

Private Sub Command3_Click()
Dim reportname As String
Dim theFilePath As String
reportname = "List"
theFilePath = “\\xxx\groupshares\xxx\”
theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportname, theFilePath, True
MsgBox "Look in your files for the report."
End Sub
Solution

  • If the folders are pre-created, would changing:

    TheFilePath = “\\xxx\groupshares\xxx\”

to

TheFilePath = “\\xxx\groupshares\xxx\” & format(Date(),"YYYY") & "\" format(date(),"yyyymm") & "\"

    do it?