vba help - Select previous day File

[Mallesh23]

Hi Team,

Need your help to open previous day file dynamically.
user keep all input file with below standard folder structure day wise.
D:\2020\09. Sep 2020\Credit_Card\01.09.2020\abc_01.09.2020.xlsx

to choose Previous day file I can go one day back using dateadd function. It works.
But if month changes Previous day file will be one day back i.e Previous months Last working day file.

How to achieve this task . Need help!

Folder :=>D:\2020\09. Sep 2020\Credit_Card\01.09.2020\abc_01.09.2020.xlsx

yyyy\mm. mmm yyyy\Credit_Card\dd.mm.yyyy\abc_dd.mm.yyyy.xlsx


My system date dd/mm/yyyy format.

added SnapShot of my systems folder structure



Thanks
mg

 

[JoeMo]

Not sure if you want the bold part of your format: yyyy\mm. mmm yyyy\Credit_Card\dd.mm.yyyy\abc_dd.mm.yyyy.xlsx to ALWAYS be the first day of the month or not.
Assuming that's what you want on the first dd, but the second dd is the prior day number, here's some code to create a dynamic file path and file name. This is easily changed to make dd the day before today for the first occurrence too.

Sub DynamicFileName()
Dim Fname As String, D As Long, DlessOne As Long, Yr As Variant, Mnth As Variant, Da As Variant
D = CLng(Date) 'Date is today's date. Can replace this with #dd/mm/yyyy# to assign any date you wish
DlessOne = CLng(D - 1)
Yr = CStr(Year(DlessOne))
Mnth = IIf(Month(DlessOne) < 10, CStr("0" & Month(DlessOne)), CStr(Month(DlessOne)))
Da = CStr(Day(DlessOne))
Fname = "D:\" & Yr & "\" & Mnth & ". " & MonthName(Month(DlessOne), True) & Year(DlessOne) & _
 "\Credit_Card\01." & Mnth & "." & Yr & "\abc_" & Da & "." & Mnth & "." & Yr & ".xlsx"
Debug.Print Fname  'Remove this line if you don't wish to print the entire filepath & name to the Immediate Window

End Sub

 

[Mallesh23]

Hi JoeMo,

Thank you for your help, your approach is Right

But in Sat and Sun no input file, if anyday there was a holiday comes there will not be Input file saved for that day.

I think need reverse loop in a folders, till we find one previous input file.


Thanks
mg

 

[Mallesh23]

Hi JoeMo,

I got your logic, I can modify as per my requirement one more help plz

how to add "0" infront of input file

abc_8.09.2020.xlsx

D:\2020\09. Sep2020\Credit_Card\01.09.2020\abc_7.09.2020.xlsx

Thanks
mg

 

[Mallesh23]

Hi JoeMo,

It worked ! modified one small change it was easy.

Millions of thank for your help!!?



Thanks
mg

 

[JoeMo]

Hi JoeMo,

It worked ! modified one small change it was easy.

Millions of thank for your help!!?



Thanks
mg

You are welcome - thanks for the reply.