CODEWITHSUNDEEP
excelvbafor-loop
user9092346
user9092346

Reputation:

Looping over an unknown number of files in folder

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

Upvotes: 0

Views: 178

Answers (3)

Sfagnum
Sfagnum

Reputation: 153

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

Upvotes: -1

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37480

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

Upvotes: 0

Related Questions