-1

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
DVEF
  • 47
  • 4
  • What is the problem you are facing when you tried this code? – tinazmu Feb 12 '22 at 10:42
  • I would like to get the report saved into the subfolders, so export it to 202101, 202102 etc without having to manually update the event procedure with the correct static folder path every month. So essentially add a piece of code behind the fixed folder address (leading to just the year folders 2021, 2022 etc.) to direct it to the correct subfolder (202101, 202102 etc.) which is the same as the name of the database itself. – DVEF Feb 12 '22 at 12:58

1 Answers1

0

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?

tinazmu
  • 3,880
  • 2
  • 7
  • 20
  • Thanks! I had tried this before posting, but it does indeed only provoke an error. Hence why I thought possibly using the database name as last bit of the TheFilePath could help. – DVEF Feb 14 '22 at 10:28
  • What is the error? Are you certain that the subfolders are already created manually before you try? – tinazmu Feb 14 '22 at 11:01
  • I don't understand, the answer, as it is, is placing the file under: `\\xxx\groupshares\xxx\2022\202202` folder, but the path in the error message you reported does not have \2022 in the middle. Why? You also say `no List`; List_... is not part of the path name, it is part of the filename. Can you confirm the full file & pathname you like to use? – tinazmu Feb 14 '22 at 12:58
  • Apologies!! I had mistakenly not created a folder with the current month yet since we work on a month-1 basis. I have now been trying to get Format(date, "YYYYMM") to return one month back, so as of today, return 202201. It works for the current month when I create the corresponding folder. – DVEF Feb 14 '22 at 13:04
  • Replace `Date` with `DateAdd("m",-1,Date)` to get the previous month. – tinazmu Feb 14 '22 at 19:47