Determine if Excel workbook exists in VBA and if it doesn't find the newest iteration

boombox · Feb 23rd 2012

Code

Sub Button1_Click()
Config = vbYesNo + vbQuestion + vbDefaultButton2
Ans = MsgBox("Crank it?", Config)
If Ans = vbYes Then
     Workbooks.Open "C:\boombox\" & Format(Date, "dd.mm.YYYY") & ".xlsx"
   
ElseIf Ans = vbNo Then
    
End If
End Sub

Hi,

I was wondering about a way to modify this code to either
1) Find out if the workbook exists, and if it doesn't, return an error
OR EVEN BETTER
2) If it doesn't, go back in time day by day until a workbook DOES exist.
i.e. Workbooks.Open "C:\boombox\" & Format(Date, "-1dd.mm.YYYY") & ".xlsx"
or
Workbooks.Open "C:\boombox\" & Format(Date, "-2dd.mm.YYYY") & ".xlsx"

THANK YOU

Wigi · Feb 23rd 2012

Re: Determine if Excel workbook exists in VBA and if it doesn't find the newest itera

For example (not tested though):

Code

Sub Button1_Click() 
    If MsgBox("Crank it?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
        i=0
        Do
           sFile = "C:\boombox\" & Format(Date-i, "dd.mm.YYYY") & ".xlsx"
           i=i+1
        Loop Until Len(Dir(sFile))
        Workbooks.Open sFile
    End If
End Sub

Please test it and give us feedback.
Add error handling too in case no such files exist.

boombox · Feb 23rd 2012

Re: Determine if Excel workbook exists in VBA and if it doesn't find the newest itera

Excellent! Thank you!

Wigi · Feb 23rd 2012

Re: Determine if Excel workbook exists in VBA and if it doesn't find the newest itera

Quote from boombox;596947

Excellent! Thank you!

No thanks

Determine if Excel workbook exists in VBA and if it doesn't find the newest iteration

Participate now!

Share