Error 1004 in for loop how to go to next function

Question

I wrote a function to import 2 to 12 excel files on one excel. The fact is that one day I have 4 files and the other I can have 6 files. Never more than 12. I did a for loop to import my files, but if I have only 4 files , when the loop looks for the 5th file It doesn't find it and there is a pop-up "error 1004". I'm trying to find a way so that my function will continu to run even if i have this error. I'd like to run a "macro #2" after my loop.

Dim d As Integer

For d = 2 To 13

Worksheets(d).Cells.ClearContents

Next d


Dim i As Integer
For i = 2 To 12


Dim file_path As String
Dim file_agg As Workbook
Dim lastrow As Long

Name = Worksheets(1).Cells(i, 1)

file_path = "C:\Users\admin\Downloads\"



Set file_agg = Workbooks.Open(file_path & Name & ".xlsx", True, True)
lastrow = file_agg.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
file_agg.Sheets(1).Range("A1:Z" & lastrow).Copy ThisWorkbook.Sheets(i).Range("A1:Z" & lastrow)
file_agg.Close SaveChanges:=False


Next i       

'macro #2 (exemple)
 .................................................
  .............................
  ............................................
   ........................

try with info from any of https://stackoverflow.com/questions/16351249/vba-check-if-file-exists https://stackoverflow.com/questions/11573914/check-if-the-file-exists-using-vba https://stackoverflow.com/questions/39999739/how-do-i-check-whether-a-file-exists-using-vba-macro-in-powerpoint https://stackoverflow.com/questions/19384483/excel-vba-function-to-detect-if-a-file-exists-based-on-cell-values — B. Go, Oct 24 '19 at 21:10

score 0 · Answer 1 · answered Oct 24 '19 at 21:10

Dim file_path As String
Dim file_agg As Workbook
Dim lastrow As Long
file_path = "C:\Users\admin\Downloads\"
Name = Worksheets(1).Cells(i, 1)
Set file_agg = Workbooks.Open(file_path & Name & ".xlsx", True, True)
dim n as long 
N=file_agg.Sheets.Count '<<<<
on error resume next 'this might work also with a fixed number 
For i = 2 To N
    lastrow = file_agg.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
    file_agg.Sheets(1).Range("A1:Z" & lastrow).Copy ThisWorkbook.Sheets(i).Range("A1:Z" 
    & lastrow) 'belongs to line above
    file_agg.Close SaveChanges:=False
Next i

Sheets.Count

If this question belongs to the number of files have a look at the DIR() function. With a placeholder they will return a array with all the files which match. This can be counted as well :) Ubound is your friend in this case. But acc. your code you want to import just one file and did not know how many sheets are in.

score 0 · Answer 2 · answered Oct 24 '19 at 21:22

Quick and dirty to skip error-causing areas... Just after starting a problem loop, add "On Error GoTo ___" and point it to the end of the loop, like this:

For i = 2 To 12
On Error GoTo SkipToNext

---- all the troublesome code causing errors goes here ----

SkipToNext:

Next i

A better solution might be based on what is in your Name variable. Maybe frame all the trouble code with:

If Name<>""
     ---- all the troublesome code causing errors goes here ----
End If

Hope one of those works for you!

score 0 · Answer 3 · answered Oct 24 '19 at 21:22

Use the FileSystemObject (you'll need to set a reference to ScriptingRuntime to use early binding and intellisense). You can loop all the files in a folder using the FileSystemObject with For Each construct.

Sub LoopFilesInFolder()
    Dim FolderPath As String
    FolderPath = "C:\Users\admin\Downloads\"
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    With fso
        Dim fldr As Folder
        Set fldr = .GetFolder(FolderPath)
    End With
    Dim fl As File
    For Each fl In fldr.Files

    Next

End Sub

Error 1004 in for loop how to go to next function

3 Answers3