Skipping non-existant files in a looped macro

Vindaloo

New Member
Joined
Jul 15, 2014
Messages
29
I am attempting to use this to pull multiple tabs from multiple workbooks. I would like this macro to skip over files that do not exist. The error manager worked for one error, but crashed on the second. I also tried the Dir method, but couldn't figure out where to put it. Any thoughts?






Code:
Sub Import()
'
' Import Macro
'

'
    Dim File_Name As String
    Dim High_Folder As String
    Dim Low_Folder As String
    Dim Path As String
    Dim i As Integer
    
    
    i = 11
   
    Application.ScreenUpdating = False
    With Worksheets("Control")
        Do Until .Cells(i, 4).Value = ""
    
    Sheets("Control").Select
    File_Name = Cells(i, 4).Value
    High_Folder = Cells(i, 5).Value
    Low_Folder = Cells(i, 6).Value
    Path = Cells(i, 7).Value
    
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    Workbooks.Open Filename:= _
        Path
        
    On Error Resume Next
    
    Sheets("WIRES-OTHER").Select
    Range("A3:F64").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("WIRES-OTHER").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SOURCE"
   Range("A2:F10000").Select
    ActiveWorkbook.Worksheets("WIRES-OTHER").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WIRES-OTHER").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("WIRES-OTHER").Sort
        .SetRange Range("A2:F10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Windows(File_Name).Activate
    Sheets("Wires Input").Select
    Rows("5:5").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("A6:G400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("Wires Input").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SETTLEMENT DATE"
   Range("A2:H10000").Select
    ActiveWorkbook.Worksheets("Wires Input").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Wires Input").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Wires Input").Sort
        .SetRange Range("A2:H10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    Windows(File_Name).Activate
    Sheets("PAT PAYMENTS").Select
    Range("A6:I400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("PAT PAYMENTS").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Date"
   Range("A2:I10000").Select
    ActiveWorkbook.Worksheets("PAT PAYMENTS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PAT PAYMENTS").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("PAT PAYMENTS").Sort
        .SetRange Range("A2:I10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    
    Windows(File_Name).Activate
    Sheets("COMM INS").Select
    Range("A6:l400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("COMM INS").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "BATCH NUM"
   Range("A2:l10000").Select
    ActiveWorkbook.Worksheets("COMM INS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("COMM INS").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("COMM INS").Sort
        .SetRange Range("A2:l10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    
    Windows(File_Name).Activate
    Sheets("CREDIT CARDS").Select
    Range("A6:l400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("CREDIT CARDS").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SETTLEMENT DATE"
   Range("A2:l10000").Select
    ActiveWorkbook.Worksheets("CREDIT CARDS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CREDIT CARDS").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CREDIT CARDS").Sort
        .SetRange Range("A2:l10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Windows(File_Name).Close savechanges:=False
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("Control").Select
    
  i = i + 1
  
    
        Loop
    End With
    
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.ScreenUpdating = False
    
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Vindaloo

New Member
Joined
Jul 15, 2014
Messages
29
I came across this thread, but I couldn't make it go on to the next row in my source list. I wanted it to increase "I" by 1 and start over. It either crashed the macro or gave me the same files multiple times.
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
I tried this example which is a slight variation on your code and it skipped the files that did not exist in my list:

Code:
[COLOR=#0000ff]Sub[/COLOR] Import()

[COLOR=#008000]' Import Macro[/COLOR]

[COLOR=#0000ff]    Dim[/COLOR] File_Name [COLOR=#0000ff]As String[/COLOR]
    [COLOR=#0000ff]Dim [/COLOR]High_Folder [COLOR=#0000ff]As String[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] Low_Folder [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] Path [COLOR=#0000ff]As String[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] i [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] TestStr [COLOR=#0000ff]As String[/COLOR]
     
    i = 11
   
    Application.ScreenUpdating = [COLOR=#0000ff]False[/COLOR]
    Worksheets("Control").Activate

[COLOR=#0000ff]    Do Until [/COLOR]Cells(i, 4).Value = ""
    
        File_Name = Cells(i, 4).Value
        High_Folder = Cells(i, 5).Value
        Low_Folder = Cells(i, 6).Value
        Path = Cells(i, 7).Value

        Application.DisplayAlerts = [COLOR=#0000ff]False[/COLOR]
        Application.AskToUpdateLinks = [COLOR=#0000ff]False[/COLOR]
        
        TestStr = ""
[COLOR=#0000ff]        On Error Resume Next[/COLOR]
        TestStr = Dir(Path & File_Name)
[COLOR=#0000ff]        On Error GoTo 0[/COLOR]
        
       [COLOR=#0000ff]     If[/COLOR] TestStr = "" [COLOR=#0000ff]Then[/COLOR]
[COLOR=#008000]                'Do Nothing[/COLOR]
[COLOR=#0000ff]            Else[/COLOR]
                Workbooks.Open Filename:=Path & File_Name
[COLOR=#0000ff]            End If[/COLOR]
        
          i = i + 1

[COLOR=#0000ff]    Loop[/COLOR]

[COLOR=#0000ff] End Sub[/COLOR]

Maybe something like this will work.

In Column D I had File Names
In Column G I had the File Path
 
Last edited:

Vindaloo

New Member
Joined
Jul 15, 2014
Messages
29

ADVERTISEMENT

Thanks for the help! This led me in the right direction. I hat to use the TestStr for each reference to opening a file:

Code:
Sub Import()
'
' Import Macro
'
'
    Dim File_Name As String
    Dim High_Folder As String
    Dim Low_Folder As String
    Dim Path As String
    Dim i As Integer
    Dim TestStr As String
    i = 11
    Skip = i
    Application.ScreenUpdating = False
    With Worksheets("Control")
        Do Until .Cells(i, 4).Value = ""
    
    Sheets("Control").Select
    File_Name = Cells(i, 4).Value
    High_Folder = Cells(i, 5).Value
    Low_Folder = Cells(i, 6).Value
    Path = Cells(i, 7).Value
    
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    
    TestStr = ""
        On Error Resume Next
        TestStr = Dir(Path)
        On Error GoTo 0
        
            If TestStr = "" Then
                'Do Nothing
            Else
    Workbooks.Open Filename:= _
        Path
            End If
    
    
    TestStr = ""
        On Error Resume Next
        TestStr = Dir(Path)
        On Error GoTo 0
        
            If TestStr = "" Then
                'Do Nothing
            Else
    Sheets("WIRES-OTHER").Select
    Range("A3:F64").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("WIRES-OTHER").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SOURCE"
   Range("A2:F10000").Select
    ActiveWorkbook.Worksheets("WIRES-OTHER").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WIRES-OTHER").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("WIRES-OTHER").Sort
        .SetRange Range("A2:F10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If
    
    TestStr = ""
        On Error Resume Next
        TestStr = Dir(Path)
        On Error GoTo 0
        
            If TestStr = "" Then
                'Do Nothing
            Else
    Windows(File_Name).Activate
    Sheets("Wires Input").Select
    Rows("5:5").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("A6:G400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("Wires Input").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SETTLEMENT DATE"
   Range("A2:H10000").Select
    ActiveWorkbook.Worksheets("Wires Input").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Wires Input").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Wires Input").Sort
        .SetRange Range("A2:H10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If
    
    TestStr = ""
        On Error Resume Next
        TestStr = Dir(Path)
        On Error GoTo 0
        
            If TestStr = "" Then
                'Do Nothing
            Else
    Windows(File_Name).Activate
    Sheets("PAT PAYMENTS").Select
    Range("A6:I400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("PAT PAYMENTS").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Date"
   Range("A2:I10000").Select
    ActiveWorkbook.Worksheets("PAT PAYMENTS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PAT PAYMENTS").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("PAT PAYMENTS").Sort
        .SetRange Range("A2:I10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If
    
    
    
    
    TestStr = ""
        On Error Resume Next
        TestStr = Dir(Path)
        On Error GoTo 0
        
            If TestStr = "" Then
                'Do Nothing
            Else
    Windows(File_Name).Activate
    Sheets("COMM INS").Select
    Range("A6:l400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("COMM INS").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "BATCH NUM"
   Range("A2:l10000").Select
    ActiveWorkbook.Worksheets("COMM INS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("COMM INS").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("COMM INS").Sort
        .SetRange Range("A2:l10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If
    
    
    
    
    
    TestStr = ""
        On Error Resume Next
        TestStr = Dir(Path)
        On Error GoTo 0
        
            If TestStr = "" Then
                'Do Nothing
            Else
    Windows(File_Name).Activate
    Sheets("CREDIT CARDS").Select
    Range("A6:l400").Select
    Selection.Copy
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("CREDIT CARDS").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "SETTLEMENT DATE"
   Range("A2:l10000").Select
    ActiveWorkbook.Worksheets("CREDIT CARDS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CREDIT CARDS").Sort.SortFields.Add Key:=Range( _
        "A3:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CREDIT CARDS").Sort
        .SetRange Range("A2:l10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Windows(File_Name).Close savechanges:=False
    Windows("DDIS Batch Reconcile.xlsm").Activate
    Sheets("Control").Select
    End If
    
  i = i + 1
  
        Loop
    End With
    
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.ScreenUpdating = False
    
End Sub
 

Vindaloo

New Member
Joined
Jul 15, 2014
Messages
29
Thanks for your help. I get better with VBA every time a new problem comes up.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,297
Messages
5,836,479
Members
430,434
Latest member
whatabout

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top