VBA Trying to Access file with biggest date

maslam15

New Member
Joined
Aug 21, 2019
Messages
24
Hello,
What I am trying to do is to open the excel file with the biggest date in the folder. The way my code works and breaks is that it subtracts one day from business day. The thing is if on Monday I run my code and no file was available on Friday my code breaks. I just need help finding the file with the biggest date.
If we have two files
My title 06-27
My title 06-28

I want to open 06-28 automatically since 28 is the file with the biggest date.
Below is the code I am currently using


Dim fpath4 As String: fpath4 = "link to the website"
Dim fName4 As String
Dim xWs4 As Worksheet




Dim wb4 As Workbook
Set wb4 = ActiveWorkbook




BD = CDate(Evaluate("WORKDAY(TODAY(),-1)"))
BD = Format(BD, "mm-dd")


fName4 = Format(Date, "mm-dd")
fName4 = "Title Here " & BD & ".xlsx"
monthAndYear = Format(Date, "mmmm" & " " & "yyyy")
fName4 = fpath4 & monthAndYear & "" & fName4
currentFile = ThisWorkbook.Name


Set wb4 = Workbooks.Open(fName4)
Set wb4 = ActiveWorkbook
Set ws4 = wb4.ActiveSheet
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Why not adjust the code so that when it is run on a Monday it subtracts 3 days instead of 1?
Code:
If Weekday(Date, vbMonday) = 1 Then 
    BD = CDate(Evaluate("WORKDAY(TODAY(),-3)"))
Else
    BD = CDate(Evaluate("WORKDAY(TODAY(),-1)"))
End If
 
Upvote 0
Subtracting 1 takes it to Friday since it skips Saturday and Sunday. The issue is what if a file is not available from Friday and was available on the date Thursday.
I want to keep subtracting 1 until it finds the file available with the biggest date
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top