Looping over an unknown number of files in folder

Question

I have a folder with Excel files which have the name is formatted "file_" & Date &_" & i & ".xls", where i is an integer between 1 and 5.

Based on the date there may be any number between 1 and 5 files in the folder:

file_01.01.2019_1.xls
file_02.01.2019_1.xls
file_02.01.2019_2.xls
file_03.01.2019_1.xls
file_03.01.2019_2.xls
file_03.01.2019_3.xls

I want to provide a date, let's say January 2nd and in a for loop open all files from that date. However, I cannot give a fixed upper value for i.

If I knew the number of files per date it would look like this:

Dim Date As Date
Date = 02.01.2019

For j = 1 To i
    Workbooks.Open "path" & "file_" & Date & "_" & j & ".xls"
    Copy and Paste Operations to another Workbook here
    Workbooks("file_" & Date & "_" & j & ".xls").Close SaveChenges:=False
Next

Please share the code you have now. You will get much better help if you show us what you have done this far — Sam, Oct 24 '19 at 06:21
Where is the code you are working on? Please add it to your question. — braX, Oct 24 '19 at 06:24
Welcome to SO! When you place a question try to add a minimum content: input sample, expected output sample, what did you try, research and where are you stuck. — David García Bodego, Oct 24 '19 at 06:24

score 1 · Answer 1 · answered Oct 24 '19 at 06:34

You can try this simple code:

Sub OpenFile()
   Dim dt As String
   dt = "02.01.2019"
   Dim fileNameStart  As String
   fileNameStart = "file_" & dt & "_"

   Dim oFile       As Object
   Dim oFSO        As Object
   Dim oFolder     As Object
   Dim oFiles      As Object
   Dim oPath       As String
   sPath = "path to your folder, with \ at the end"

   Set oFSO = CreateObject("Scripting.FileSystemObject")
   Set oFolder = oFSO.GetFolder(sPath)
   Set oFiles = oFolder.Files

   If oFiles.Count = 0 Then Exit Sub

   For Each oFile In oFiles
        If InStr(1, oFile.Name, fileNameStart) = 1 Then Workbooks.Open sPath & oFile.Name
   Next

End Sub

Sfagnum · Accepted Answer · 2019-10-24T10:43:37.787

0

Check in loop is file exists for each day.
vba file exists

ok. code will be something like this: NB!! Function IsFile(ByVal fName As String) As Boolean (R)

Dim i as integer
Dim dirpath as string
Dim sDate as string
dirpath = "C:\Path\To\Needed\Folder\"
sDate = "02.01.2019"
for i = 1 to 5
    if IsFile(dirpath & "file_" & sDate & "_" & i & ".xls") then
         'do something
    end if
next

Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
    On Error Resume Next
    IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function

edited Oct 24 '19 at 10:43

answered Oct 24 '19 at 06:31

Sfagnum

163
8

code in `Function IsFile` appears to be plagiarized from [this answer](https://stackoverflow.com/a/28237845/839601) that was posted several years ago – gnat Oct 24 '19 at 10:15
yes, I know. I new hear and don't know how correctly use links to the answer. And function take exactly from that answer. Hear is many same functions, I wanted to show a bit another. – Sfagnum Oct 24 '19 at 10:39

score 0 · Answer 3 · answered Oct 24 '19 at 10:12

a different approach would benefit from an aware use of On Error Resume Next statement in the following function:

Function OpenWb(wbFullName As String, wb As Workbook) As Boolean
    Set wb = Nothing
    On Error Resume Next
    Set wb = Workbooks.Open(wbFullName)
    OpenWb = Not wb Is Nothing
End Function

which returns False in case not existing workbook and True if workbook found, along with its (valid) wb reference, and which would therefore be used as follows:

Dim Data As String
Data = "02.01.2019"

Dim path As String
path = "full folder path"

Dim j As Long
Dim wb As Workbook
For j = 1 To 5
    If OpenWb(path & "\file_" & Data & "_" & j & ".xls", wb) Then
        With wb ' reference opened workbook
            'Copy and Paste Operations from 'wb' to another Workbook here
            'for instance:
            .Worksheets("WbWorksheetName").Range("WbRange").Copy Destination:=otherWb.Worksheets("otherWbWorksheetName").Range("otherWbRange")

            .Close SaveChanges:=False
        End With
    End If
Next

Looping over an unknown number of files in folder

3 Answers3