Skipping non-existant files in a looped macro

Vindaloo

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?






PHP: 
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 does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dazzawm
  • Question
Change Recorded Range To Used Range.
2
Replies
12
Views
357
Dazzawm
Dazzawm
Dazzawm
  • Solved
Slight Amendment To Code
Replies
6
Views
288
Dazzawm
Dazzawm
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
200
Coco1
C
SandsB
  • Question
Sorting when a column or 2 is empty
Replies
1
Views
151
DanteAmor
DanteAmor
J
  • Question
Rename a Sheet
Replies
2
Views
100
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,653
Messages
6,120,751
Members
448,989
Latest member
mariah3

Share this page

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
Back
Top