Open yesterdays file

[DMT Dave]

Hi Guys, i am wanting open yesterdays file if today's file is not yet in the folder, the following debugs if it is trying to open yesterdays (yDate) file but opens fine when it is today's file (tDate)



If Me.cboFileOptions = "Open Daily Update" Then
strPathName = "T:\Shared\XL Files\Daily Update"
tDate = Forms!frmMainMenu!txtUpdate
yDate = DateAdd("d", -1, tDate)
If Dir(strFilePath & FileName) <> "" Then
FileName = "Update For" & " " & Format(tDate, "dd-mm-yy") & ".xlsx"
Else
FileName = "Update For" & " " & Format(yDate, "dd-mm-yy") & ".xlsx"
End If
PathToOpen = strPathName
FileToOpen = FileName
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open PathToOpen & FileToOpen, True, False
End If

[ranman256]

Filename has not been defined yet here:

If Dir(strFilePath & Filename) <> "" Then

you cant test before it has a value.
(and the foldername)
Maybe:

If Me.cboFileOptions = "Open Daily Update" Then
    strPathName = "T:\Shared\XL Files\Daily Update\"
    tDate = Forms!frmMainMenu!txtUpdate
    yDate = DateAdd("d", -1, tDate)
    
    Filename = "Update For" & " " & Format(yDate, "dd-mm-yy") & ".xlsx"
    If Dir(strFilePath & Filename) = "" Then Filename = "Update For" & " " & Format(tDate, "dd-mm-yy") & ".xlsx"
    
    PathToOpen = strPathName
    FileToOpen = Filename
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open PathToOpen & FileToOpen, True, False
End If

[ranman256]

or:
If FileExists(filename)

Public Function FileExists(ByVal pvFile) As Boolean
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FileExists = FSO.FileExists(pvFile)
Set FSO = Nothing
End Function

[accesstos]

I would do it in this way:

'--[...]-----------------------------------------------------------------------------------------------------
    strFileName = Dir(strPathName & "UpdateFor_" & Format(Nz(Forms!frmMainMenu!txtUpdate, Date), "yyyymmdd"))
    i = 0
    Do While (Len(strFileName) = 0) And i < 6
        i = i + 1
        strFileName = Dir(strPathName & "UpdateFor_" & Format(Date - i, "yyyymmdd"))
    Loop
    If Len(strFileName) Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        xlApp.Workbooks.Open PathToOpen & FileToOpen, True, False
    Else
        MsgBox "No updated files found!", vbExclamation
    End If
'--[...]-----------------------------------------------------------------------------------------------------

It looking for last updated file in the last week.
I prefer the "yyyymmdd" format as suffix at (non spaces) file names.

Best regards,

John