Skip a 'file not found' dialogue in a loop

[dicenn]

Hi,

I am having a problem with a portion of a macro which is designed to loop through the file names of several files and open them all. The way it works is that you would set a start date and end date for the files the macro should open, then run. However, if one of the files happens to be missing, the macro will stop working. For example, I am looping through files from December 24th to December 31st, but there is no file for December 24th.

I want to avoid requiring the user to indicate dates for which the files are missing, also I have tried a GoTo statement for error handling, however the macro not finding the file does not seem to be considered an error. What is a workaround that would automatically skip one step in the loop when it doesn't encounter a file with the name that it is trying to open? My code is below.

Much Appreciated,

T

Public MainPath, Fldr, PWSource, ThisFile As String
Public NumDays, Now As Long
Sub LoopTest()
'
' LoopTest Macro
'

'
    
MainPath = Range("A2")
MacPath = Range("A3")
Fldr = Range("A6")
PWSource = Range("A9")
ThisFile = Range("F2")
NumDays = Range("F11")
Now = Range("F12")
    
'Open the first sheet

    ChDir _
        MainPath & Fldr
    
    Workbooks.Open Filename:= _
        MainPath & Fldr & PWSource
    
'set the value of check variable to 1 to start
    
    Windows(ThisFile).Activate
    
    Range("F12") = Now + 1
    Now = Range("F12")
   
'loop through sheets and combine the data into one
    
    For Now = 1 To NumDays
   
    Windows(ThisFile).Activate

    MainPath = Range("A2")
    Fldr = Range("A6")
    PWSource = Range("A9")
  
    Workbooks.Open Filename:= _
        MainPath & Fldr & PWSource
       
'add 1 to check variable

    Windows(ThisFile).Activate
    Range("F12") = Now + 1

Next

'reset check variable
    
    'Windows(ThisFile).Activate
    'Range("F12") = 0
    'Now = Range("F12")

End Sub 

[Norie]

You can check if a file exists using Dir.

PS Now is a VBA statement and shouldn't be used for the name of a variable.

[dicenn]

That's awesome! My code is a lot shorter and does exactly what I needed by combining an if statement with dir.

Thanks!

Public MainPath, Fldr, PWSource, ThisFile As String
Public NumDays, Begin As Long
Public CheckDate As Date
Sub LoopTest()
'
' LoopTest Macro
'

'
    
MacPath = Range("A3")
ThisFile = Range("F2")
NumDays = Range("F11")
Begin = Range("F12")
CheckDate = Range("E14")
    
    Application.DisplayAlerts = False

'loop through books and open
    
    For Begin = 0 To NumDays
    
    MainPath = Range("A2")
    Fldr = Range("A6")
    PWSource = Range("A9")
    CheckDate = Range("E14")

'check if file exists

If Len(Dir(MainPath & Fldr & PWSource)) <> 0 Then
    
    Workbooks.Open Filename:= _
        MainPath & Fldr & PWSource
   
    Windows(ThisFile).Activate

    Range("F12") = Begin + 1
    
Else

    Range("F12") = Begin + 1

End If

Next

'reset check variable
    
    Windows(ThisFile).Activate
    Range("F12") = 0
    Begin = Range("F12")

End Sub