Open Workbook With Most Recent Date in Filename

[Small Paul]

Hi

I have a number of files saved each day, with either the current date or previous working day date in the filename.

To open yesterday's workbook is now no problem:

    Workbooks.Open Filename:= _
        "Z:\Secondary Market\Pricing\Daily Index Levels\Index Levels " & Format(Now() - 1, "yyyy-mm-dd") & ".csv"

However, on a Monday, I need Friday's file which is "-3".

What is the logic to say 'if yesterday's does not exist, open another'?

At the moment, I have the following snippet of code.

    Workbooks.Open Filename:= _        "Z:\Secondary Market\Pricing\Daily Index Levels\Index Levels " & Format(Now() - 1, "yyyy-mm-dd") & ".csv"
If wb Is Nothing Then
    Workbooks.Open Filename:= _
        "Z:\Secondary Market\Pricing\Daily Index Levels\Index Levels " & Format(Now() - 3, "yyyy-mm-dd") & ".csv"
        End If

Many thanks
Small Paul.

 

[gallen]

Hello,

This should do it. Untested so let me know.

Sub OpenPreviousWorkbook()


    'number of days from and to
    Dim i As Integer, iNumberofDays As Integer
    
    Dim wb As Workbook 'workbook variable
    
    'maximum number of days previous to search. Adjust accordingly
    iNumberofDays = 7
    
    'look back a maximum of seven days? increase the 7 to number
    For i = 1 To iNumberofDays
    
        'error will occur if path/file doesn't exist so ignore
        On Error Resume Next
        
        'attempt to set the vairable
        Set wb = Workbooks.Open("Z:\Secondary Market\Pricing\Daily Index Levels\Index Levels " & Format(Now() - i, "yyyy-mm-dd") & ".csv")
        
        'dont ignore errros
        On Error GoTo 0
        
        'if the workbook is set exit the loop
        If Not wb Is Nothing Then Exit For
        
    Next i
    
    'if we get here and Wb is nothing then we couldn't find a file
    If wb Is Nothing Then MsgBox "Failed to set. Check path"
    
End Sub

 

[Small Paul]

Hi Gallen
I have copy/pasted your coding.
I am getting a "Compile error: Expected End Sub" at the quotation mark before "Sub Open PreviousWorkbook()

    Range("B2:B150").Select    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
   
    End Sub


Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+t
'
Sub OpenPreviousWorkbook()
 
    'number of days from and to
    Dim i As Integer, iNumberofDays As Integer
    
    Dim wb As Workbook 'workbook variable
    
    'maximum number of days previous to search. Adjust accordingly
    iNumberofDays = 7

By the way, the top part is the end of my Macro6

 

[gallen]

I'm confused, check the code you've posted and the code I've posted....

 

[footoo]

Delete, or comment out, this : Sub Macro7()

 

[Small Paul]

Hi Gallen & Footoo
Sorry for delay but been away on a conference.
A bit of 'jiggery-pokery' but it now works (dreading a bank holiday though!).
Many thanks to you both.
Small Paul.