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:
VBA Code:
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