Looping through all files in a folder with specific name

[Niraj Baraili]

Hi Experts,

Need your help on this.

I need to loop through all the files in a folder with specific names (names containing L1) . My files are as below:

01 L1.xlsx
01 L2.xlsx
02 L1.xlsx
02 L2.xlsx
03 L1.xlsx
03 L2.xlsx
04 L1.xlsx
04 L2.xlsx

 

[Marc L]

Hi,

just use Dir VBA function, see sample in VBA inner help
as well in yours own past threads …
​

 

[Niraj Baraili]

Hi Marc,

i tried below code:

Sub Test()
Dim path As String
Dim Fname As String
path = "C:\Users\niraj.baraili\Desktop\Automation\Login Logout Report\Aux dump\"
Fname = "*" & " L1.xlsx"


myfile = Dir(path & Fname)

Do While myfile <> 0
   

    Workbooks.Open (myfile)

myfile = Dir
Loop


End Sub

 

[Niraj Baraili]

Sorry missed the line. It's throwing up error 1004.

 

[Chihiro]

myfile variable will only contain file name and it lacks path. You need to concatenate with path.

 

[Niraj Baraili]

Hi Chihiro,

Thanks for the guidance. But i am still confused. Could you please show me the disconnect ?

 

[Chihiro]

Ok. Lets say you have following files in path (i.e. folder).
01 L1.xlsx
01 L2.xlsx
02 L1.xlsx
02 L2.xlsx
03 L1.xlsx
03 L2.xlsx
04 L1.xlsx
04 L2.xlsx

If you debug.print myfile during each iteration of the loop, it will only show "xx L1.xlsx" as string. Therefore when using Workbooks.Open you need to concatenate path & myfile.

Workbooks.Open (path & myfile)

Also Do While check should check for NullString instead of "0" and just in case path is the same folder that the workbook with the code is in... I would add check for that. So resulting code would be something like...

Sub Test()
Dim path As String
Dim Fname As String
path = "C:\Users\niraj.baraili\Desktop\Automation\Login Logout Report\Aux dump\"
Fname = "*" & " L1.xlsx"

myfile = Dir(path & Fname)

Do While myfile <> ""
    If Not myfile = ThisWorkbook.Name Then
        Workbooks.Open (path & myfile)
    End If
myfile = Dir
Loop

End Sub

 

[Niraj Baraili]

Hi Chihiro,

Excellent. Thank you very much.