Conditional formula to open file

[noexit]

I have a file name saved by date, and I'd like to make a conditional formula to check to see if there's a file name with the current date in it, and if not, open the previous day's file. This code will open the two files.

Sub OpenFile()

Dim strYesterdayFile As String
Dim strTodayFile As String

strFileName = "File " & Format(DateAdd("d", -1, Date), "mm.dd.yy") & ".xls"
strTodayFile = "File " & Format(Now(), "mm.dd.yy") & ".xls"

Workbooks.Open Filename:=strYesterdayFile
Workbooks.Open Filename:=strTodayFile

Sub End

How do I use an If Then statement to try to open today's file, and if it doesn't exist, then open yesterday's file? To complicate things further, it would also be nice to skip weekends, so on Monday, to open Friday's file.

 

[SkipVought]

Hi,

Try a loop

Sub OpenFile()
    Dim i As Integer
    Dim strYesterdayFile As String
    Dim strTodayFile As String
    
    On Error Resume Next
    
    i = -1
    Do
        Err.Clear
        strFileName = "File " & Format(DateAdd("d", i, Date), "mm.dd.yy") & ".xls"
        Workbooks.Open Filename:=strFileName
        i = i - 1
    Loop While Err.Number <> 0
    
    strTodayFile = "File " & Format(Now(), "mm.dd.yy") & ".xls"
    Workbooks.Open Filename:=strTodayFile
End Sub

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Bong]

create a FileSystemObject:

Set fs = CreateObject("Scripting.FileSystemObject")

Then use its FileExists method:

if fs.FileExists(strTodayFile) then
  Workbooks.Open Filename:=strTodayFile
else
  Workbooks.Open Filename:=strYesterdayFile
end if

_________________
Bob Rashkin

 

[noexit]

Bongs code works great. Now how about on a monday or after a holiday? On another project, I just made a input box pop up asking for the previous day's date, stored that as a string, and used it when the previous day's date was needed. That seems the simplest right now, but if I can find a more elegant way, I might as well try.

 

[SkipVought]

Did you try the loop?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[PHV]

Why not simply play with the Dir function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noexit]

Okay, the loop worked, but the example above opened both today's date, and the previous date. I modified it as follows.

Sub OpenFile()
    Dim i As Integer
    Dim strFileName As String
    
    On Error Resume Next
    
    i = 0
    Do
        Err.Clear
        strFileName = "File " & Format(DateAdd("d", i, Date), "mm.dd.yy") & ".xls"
        Workbooks.Open Filename:=strFileName
        i = i - 1
    Loop While Err.Number <> 0
End Sub

Making i = 0 made it start with today's date, making the lines to open the current dated file at the end unnessisary.

 

[SkipVought]

Glad that the code worked for you.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}