I want to get the file on directory counting 21 previous days from today

Question

I have a directory with subdirectories that contains monthly files archived. I want to get the data from files from subdirectory counting 21 previous days from today and if is not in my current subdirectory, then skip to previous subdirectory.

The way I declared, brings me the content that I need, however with exactly the currently month, number corresponding of month, and year.

Sub getNumber(CM As String, shift As String, fileDate As String)
Dim newDate: newDate = Format(DateAdd("M", 0, Now), "MM-MMMM")
filePath ="\\bt\depthr\cement\trailer\Plans\Day Shift\05-May\trailer_flow_5-5-22 Day Shift Plan.xlsb\" & newDate & "\"
fileName = CM & "_" & "flow" & "_" & fileDate & "Plans" & ".xlsb"```

How can I correct it to bring me previous 21 days and skip to previous subdirectory if the 21 days includes previous month?

Thank you.

\\bt\dept\cement\trailer\Plans\Day Shift\05-May\trailer_flow_5-5-22 Day Shift Plan.xlsb — David Knight, May 11 '22 at 14:56
Please [edit your question](https://stackoverflow.com/posts/72203144/edit) to add information — cybernetic.nomad, May 11 '22 at 15:26
Something like `myPath = "\\bt\dept\cement\trailer\Plans\Day Shift\" & Format(Date - 21, "MM-MMMM") & "\trailer_flow_" & Format(Date - 21, "m-d-yy") & " Day Shift Plan.xlsb"` maybe? — cybernetic.nomad, May 11 '22 at 15:31
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/244674/discussion-between-cybernetic-nomad-and-david-nunes). — cybernetic.nomad, May 11 '22 at 15:59

score 0 · Answer 1 · answered May 11 '22 at 18:14

0

This will give you 21 days back date in VBA.

Dim newD As Date
newD = Date - 21
MsgBox newD

answered May 11 '22 at 18:14

Naveed

1
3

Yes it does!!! I got it!!! now I will need to create a if condition to take me back if the 21 days file is inside the last month directory. – David Knight May 11 '22 at 18:46
Thank you Naveed. I am using your suggestion successfully. – David Knight May 12 '22 at 15:09

I want to get the file on directory counting 21 previous days from today

1 Answers1