vba help - Select previous day File

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
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
 

Attachments

  • Input file -in folder snapshot.png
    Input file -in folder snapshot.png
    67.1 KB · Views: 3

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi JoeMo,

It worked ! modified one small change it was easy.

Millions of thank for your help!!🕺



Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top