VBA Loop To Import Changing File Names

Question

I am looking for a code to search a directory for specific file names with a changing number at the end of the name (File_1.xls, File_2.xls, File_3.xls, etc.) and stack the data within the reports on top of eachother without headers into a tab but if a File_Amend.xls file exists then it will only copy the data from that file and paste it into it's own tab. The only changing part of the File_ is 1,2,3, etc. or Amend. Everything ends in .xls

I've gotten this far:

Sub SaveFile()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim File As String
Dim wsCopy As Worksheet
Dim wsCopy2 As Worksheet
Dim wsCopy3 As Worksheet
Dim wsPaste As Worksheet

' For this part I am looking to have the file name constant as "File_" and then have the code search for files with the numbers 1,2,3,4, etc. instead of hardcoding in the file name 

File = "L:\Main\Code\"

Set wsCopy = File & wb.Sheets("Main").Range("C6") 'this value is "File 1.xls"
Workbooks.Open Filename:=wsCopy, ReadOnly:=True

Set wsCopy2 = File & wb.Sheets("Main").Range("C7") 'this value is "File 2.xls"
Workbooks.Open Filename:=wsCopy2, ReadOnly:=True

Set wsCopy3 = File & wb.Sheets("Main").Range ("C8") 'this value is "File Amend.xls"
Workbooks.Open Filename:=wsCopy3, ReadOnly:=True

Set wb = Workbooks.Add
Set wsPaste = wb.Sheets(1)

If Dir(wsCopy) = True Then
wsCopy.Range ("A:I").Copy
wsPaste.Cells.PasteSpecial Paste:=xlPasteValues

If Dir(wsCopy2) = True Then
wsCopy2.UsedRange.Offset(1,0).SpecialCells(xlCellTypeVisible).Copy
wsPaste.Cells (Rows.Count, "A").End(x1Up).Offset (1, 0).PasteSpecial Paste: xlPasteValues

If Dir(wsCopy3) = True Then
wsPaste.Cells.ClearContents
wsCopy3.Range("A:I").Copy
wsPaste.Range("Al").PasteSpecial Paste:=xlPasteValues

End Sub

I'm specifically stuck trying to figure out a loop that would be able to count for each file name + 1 using just "File_" as the constant and cycle through each item that exists so File_1, File_2, File_3, etc. and copy and paste the data and then how to incorporate a condition where if Amend exists only take the data from that report and paste it into the report alone — Mabel, Jan 11 '23 at 17:32
[This may be useful](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) — cybernetic.nomad, Jan 11 '23 at 17:43

score 1 · Accepted Answer · answered Jan 11 '23 at 18:08

For searching filenames with different numbers, you can use different constant "File", and FOR loop. Special check for File_Amend.xls can be put before the above code, all like this:

If Dir ("L:\Main\Code\File_Amend.xls" = True then
   ...
Else
   File = "L:\Main\Code\File_"
   For i = 1 to 99
      wsCopy = File & i & ".xls"
      ...
   Next i
End if

VBA Loop To Import Changing File Names

1 Answers1