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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,456
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,222
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top