Loop Through Folder and Take Specific Actions Based on Filename

[reberryjr]

I've looked around the web a bit, but I'm struggling to find a solution that fits my needs. Essentially, I want to be able to push a button and have VBA tap out to a specific folder and loop through each excel file within that folder and take certain actions based on the filename. There could be different versions of a file (e.g., Apple1, Apple2, Apple7, etc.). If I'm reaching out for a particular file, I can code to that, but getting the code to read the filename so it knows what actions to take is throwing me off. Here's what I have so far:

Sub ImportDataFiles()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mI As Worksheet, mV As Worksheet, mN As Worksheet, mO As Worksheet, mP As Worksheet
Dim mILR As Long, mVLR As Long, mNLR As Long, mOLR As Long, mPLR As Long
Dim fP As String, fF As String, fE As String

Set m = ThisWorkbook
Set mI = m.Sheets("CC_I")
Set mV = m.Sheets("CC_V")
Set mN = m.Sheets("CC_N")
Set mO = m.Sheets("OP")
Set mP = m.Sheets("P")

'Sets the Tool's input folder location.
fP = ("\\Network Shared Drive\") 'Actual path removed for security reasons.

'Declares the target files' extension as Excel.
fE = "*.xls*"

fF = Dir(fP & fE)

'Loop through the Tool's input folder and import data files.
Do While fF <> ""

    If fF = "Apple.xls*" Then
        mI.Range("A4") = "Red"
    Else
        If fF = "Banana.xls*" Then
            mI.Range("A7") = "Yellow"
        End If
    End If

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Jeffrey Mahoney]

Please try this. The DIR part needs to be in the loop. As you can see when you call DIR the first time, you need to specify the search criteria. Any subsequent calls to DIR need to be blank so that it can look at all the rest of the files.

Sub ImportDataFiles()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mI As Worksheet, mV As Worksheet, mN As Worksheet, mO As Worksheet, mP As Worksheet
Dim mILR As Long, mVLR As Long, mNLR As Long, mOLR As Long, mPLR As Long
Dim fP As String, fF As String, fE As String, dF As String
Dim X As Long

Set m = ThisWorkbook
Set mI = m.Sheets("CC_I")
Set mV = m.Sheets("CC_V")
Set mN = m.Sheets("CC_N")
Set mO = m.Sheets("OP")
Set mP = m.Sheets("P")

'Sets the Tool's input folder location.
fP = ("\\Network Shared Drive\") 'Actual path removed for security reasons.

'Declares the target files' extension as Excel.
fE = "*.xls*"

fF = fP & fE

'Loop through the Tool's input folder and import data files.
X = 0
Do
    X = X + 1
    If X = 1 Then
      dF = Dir(fF)
    Else
      dF = Dir()
    End If
    If dF = "" Then Exit Do
    
    If InStr(dF, "Apple") Then
      mI.Range("A4").Value = "Red"
    ElseIf InStr(dF, "Banana") Then
      mI.Range("A7").Value = "Yellow"
    End If

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[reberryjr]

Please try this. The DIR part needs to be in the loop. As you can see when you call DIR the first time, you need to specify the search criteria. Any subsequent calls to DIR need to be blank so that it can look at all the rest of the files.

Sub ImportDataFiles()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mI As Worksheet, mV As Worksheet, mN As Worksheet, mO As Worksheet, mP As Worksheet
Dim mILR As Long, mVLR As Long, mNLR As Long, mOLR As Long, mPLR As Long
Dim fP As String, fF As String, fE As String, dF As String
Dim X As Long

Set m = ThisWorkbook
Set mI = m.Sheets("CC_I")
Set mV = m.Sheets("CC_V")
Set mN = m.Sheets("CC_N")
Set mO = m.Sheets("OP")
Set mP = m.Sheets("P")

'Sets the Tool's input folder location.
fP = ("\\Network Shared Drive\") 'Actual path removed for security reasons.

'Declares the target files' extension as Excel.
fE = "*.xls*"

fF = fP & fE

'Loop through the Tool's input folder and import data files.
X = 0
Do
    X = X + 1
    If X = 1 Then
      dF = Dir(fF)
    Else
      dF = Dir()
    End If
    If dF = "" Then Exit Do
   
    If InStr(dF, "Apple") Then
      mI.Range("A4").Value = "Red"
    ElseIf InStr(dF, "Banana") Then
      mI.Range("A7").Value = "Yellow"
    End If

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

@Jeffrey Mahoney thank you for the response! I must be doing something incorrectly because i can get the value of "Yellow" but not "Red". I've checked the file name and it's accurate. Oddly enough the actual file name has 3 words (Apple Pear Grape). If I replace "Apple" with "Pear" I don't get any results, but if I replace "Apple" with "Grape", I get the desired results. Thoughts?

Side note, no these aren't real filenames.

 

[Jeffrey Mahoney]

Yes, You need to use the method I gave with the InStr(dF, "Apple"). In the example code you gave above: If fF = "Apple.xls*" Then is only going to return files where 'Apple' is at the end of the name. INSTR can find apple anywhere in the string.

 

[reberryjr]

Yes, You need to use the method I gave with the InStr(dF, "Apple"). In the example code you gave above: If fF = "Apple.xls*" Then is only going to return files where 'Apple' is at the end of the name. INSTR can find apple anywhere in the string.

Prior to responding yesterday, I basically copy and pasted your suggestion into a module and changed the fake variables to real ones. The part about getting the desired results when I used the last word in the filename was just me experimenting. I've checked the pathing and it's accurate. I've checked my sheet references and they're accurate. I'm not sure where I'm going wrong here.

 

[Alex Blakenburg]

Does it make any difference if you change it to the below ?

    If InStr(1, dF, "Apple", vbTextCompare) Then
      mI.Range("A4").Value = "Red"
    ElseIf InStr(1, dF, "Banana", vbTextCompare) Then
      mI.Range("A7").Value = "Yellow"
    End If

 

[reberryjr]

Does it make any difference if you change it to the below ?

    If InStr(1, dF, "Apple", vbTextCompare) Then
      mI.Range("A4").Value = "Red"
    ElseIf InStr(1, dF, "Banana", vbTextCompare) Then
      mI.Range("A7").Value = "Yellow"
    End If

Unfortunately it does not.

 

[Alex Blakenburg]

Then you are going to have to give us a more realistic example of your file name and your if statement so we can figure out why it wouldn't be working.

 

[reberryjr]

Then you are going to have to give us a more realistic example of your file name and your if statement so we can figure out why it wouldn't be working.

Here is the code almost exactly as is in my module. The only difference is the folder path is masked here. I also changed the destination ranges to a sheet that doesn't have any data in it.

Sub ImportDataFiles()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mI As Worksheet, mV As Worksheet, mN As Worksheet, mO As Worksheet, mP As Worksheet
Dim mILR As Long, mVLR As Long, mNLR As Long, mOLR As Long, mPLR As Long, x As Long
Dim fP As String, fF As String, fE As String, dF As String

Set m = ThisWorkbook
Set mI = m.Sheets("CC_I")
Set mV = m.Sheets("CC_V")
Set mN = m.Sheets("CC_N")
Set mO = m.Sheets("OP")
Set mP = m.Sheets("P")

'Sets the Tool's input folder location.
fP = ("\\Removed for security\")
     
'Declares the target files' extension as Excel.
fE = "*.xls*"

fF = Dir(fP & fE)

'Loop through the Tool's input folder and import data files.
x = 0
Do
    x = x + 1
    If x = 1 Then
        dF = Dir(fF)
    Else
        dF = Dir()
    End If
    
    If dF = "" Then Exit Do
    
    If InStr(1, dF, "Average Approval Time", vbTextCompare) Then
        mO.Range("P4").Value = "AAT"
    ElseIf InStr(1, dF, "Tom_SLA", vbTextCompare) Then
        mO.Range("P12").Value = "SLA"
    End If

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Jeffrey Mahoney]

Prior to responding yesterday, I basically copy and pasted your suggestion into a module and changed the fake variables to real ones. The part about getting the desired results when I used the last word in the filename was just me experimenting. I've checked the pathing and it's accurate. I've checked my sheet references and they're accurate. I'm not sure where I'm going wrong here.

You should debug the code. Step through the code and when it gets to the part where the filename is being evaluated, hover the mouse over the variable to display what it is providing.

TAP F8 one line at a time