Macro to open a different file if the first can't be found

[03aviator]

I run a macro daily which looks up a dated file (with today's date in the name) and opens it, extracts data, then closes it. My problem is sometimes the file hasn't been created. Sometimes a new file won't be created for two or three days. If it can't find today's file then I want it to look for and use the file with the previous day's date in the name. If it can't find that one either, then I would like it to use the file from two days ago.

Please help - I am still learning VBA and I'm not sure how to do this. The main reason for the check and then change of file name is so the macro doesn't stop - but looks for another file so it can keep running.

The file name is: 11-09-30 Vendor Inventory Reports LRC
The previous day's file would be: 11-09-29 Vendor Inventory Reports LRC
and so on

 

[klarowe]

Could you post up your current code?

 

[03aviator]

Here's the code for the entire macro, the error occurs when it try's to open the file and it can't find the name. I created three additional file names for the previous days files, but I don't know how to set the code to check for the different names if it can't find the first name. I want them checked in sequence as I want the latest version that exists to be used. The file name in question is BOAAgingFileName. All the names are located on a separate sheet in the wookbook.

Sub Import_BOAAging()
'
' Import_BOAAging Macro
' Recorded by JKlecker 9/28/2011
'
Dim YearDirectory As String
Dim MonthDirectory As String
Dim BucketFileName As String
Dim BOAAgingFileName As String
Dim BOAAgingFileName2 As String
Dim BOAAgingFileName3 As String
Dim BOAAgingFileName4 As String

'
' Define variables
YearDirectory = Sheets("Formulas").Range("D38")
MonthDirectory = Sheets("Formulas").Range("D39")
BucketFileName = Sheets("Formulas").Range("D10")
BOAAgingFileName = Sheets("Formulas").Range("D40")
BOAAgingFileName2 = Sheets("Formulas").Range("D41")
BOAAgingFileName3 = Sheets("Formulas").Range("D42")
BOAAgingFileName4 = Sheets("Formulas").Range("D43")

' Clear old data
Sheets("BOA Aging").Select
Range("A2:A15000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Range("C2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Range("A1").Select

' Open data file
Workbooks.Open Filename:= _
"O:\Data\Reports\BOA Reports\" & YearDirectory & MonthDirectory & BOAAgingFileName

Range("H55").Select
Selection.End(xlDown).Select
Selection.ShowDetail = True
' Copy first set of data
Range("D:D").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.copy

Windows(BucketFileName).Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
' Get more data
Windows(BOAAgingFileName).Activate
Range("J2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy

Windows(BucketFileName).Activate
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
' Get more data
Windows(BOAAgingFileName).Activate
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.copy

Windows(BucketFileName).Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
' Close BOA Aging File
Windows(BOAAgingFileName).Activate
Application.CutCopyMode = False
SendKeys "{n}", False
ActiveWindow.Close

' Adjust Aged Notes formula rows
' To adjust formula rows in worksheet to match data rows
'
Dim BACopyRange As String
Dim BAAdjustRange As String
Dim BOAAgingFullRange As String
'
BACopyRange = Sheets("BOA Aging").Range("P3")
BAAdjustRange = Sheets("BOA Aging").Range("P4")
BOAAgingFullRange = Sheets("BOA Aging").Range("P5")
'
If Range("L5") = "0" Then

Range("A1").Select

End If
If Range("L5") > "0" Then

Range(BACopyRange).Select
Selection.copy
Range(BAAdjustRange).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
ElseIf Range("L5") < "0" Then

Range(BAAdjustRange).Select
Selection.ClearContents
Range("A1").Select

End If
' Copy formula down
Range("I2:J2").Select
Application.CutCopyMode = False
Selection.copy
Range(BOAAgingFullRange).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.copy
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

' Copy formula in Bucket data section
Application.Run "Copy_BA_Aging"

Sheets("Pivots").Select

End Sub