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

 

[reberryjr]

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

Here's what's happening...

    x = x + 1
    If x = 1 Then
        dF = Dir(fF)  'Returns the file name during debugging.
    Else
        dF = Dir()
    End If
    
    If dF = "" Then Exit Do 'Exits the sub here.

 

[Jeffrey Mahoney]

So you didn't get an error?

To explain that code again. df = DIR(ff) looks for the first file in the series with the criteria provided. df = DIR() finds subsequent files that match criteria. if df results to nothing, then the loop has found it's last file.

 

[reberryjr]

So you didn't get an error?

To explain that code again. df = DIR(ff) looks for the first file in the series with the criteria provided. df = DIR() finds subsequent files that match criteria. if df results to nothing, then the loop has found it's last file.

No error, but it didn't perform this action:

    If InStr(1, dF, "Average", vbTextCompare) Then
        mO.Range("P4").Value = "P"

 

[Jeffrey Mahoney]

I just want to sit down at your desk and dig into the code

Without seeing a list of files and the path you are providing, I'm blind. Can you add a line of code inside the DO loop: Debug.print Df

That will give us an idea of what files it IS looking at. Just copy the values from the immediate window below the code.

 

[reberryjr]

I just want to sit down at your desk and dig into the code

Without seeing a list of files and the path you are providing, I'm blind. Can you add a line of code inside the DO loop: Debug.print Df

That will give us an idea of what files it IS looking at. Just copy the values from the immediate window below the code.

@Jeffrey Mahoney sooooo....I'm a moron. I went back and compared your suggested code to what I was running, line by line. I think I found the issue. I had

fF = Dir(fP & fE)

instead of

fF = fP & fE

 

[reberryjr]

@Jeffrey Mahoney sooooo....I'm a moron. I went back and compared your suggested code to what I was running, line by line. I think I found the issue. I had

fF = Dir(fP & fE)

instead of

fF = fP & fE

Actually, maybe I didn't fix the issue completely. Data is importing, but it appears that the same file is being imported multiple times. Example, if the following files are in the target folder, it appears only 1 of the files is being imported multiple times.

GreenApple080123.xlsx
GreenApple070123.xlsx

 

[Jeffrey Mahoney]

Post your latest code. I will look at it immediately

 

[reberryjr]

Post your latest code. I will look at it immediately

My apologies for the delay. I was away from my desk for the rest of the day and didn't feel like logging back on once I got home. Here is what I have now:

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

'Sets the Tool's input folder location.
fP = ("\\Path is Masked\")
     
'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(1, dF, "Average", vbTextCompare) Then
        Call ImportPData
    ElseIf InStr(1, dF, "ORC", vbTextCompare) Then
        Call ImportCCPData
    ElseIf InStr(1, dF, "Quality", vbTextCompare) Then
        Call ImportCCIData
    ElseIf InStr(1, dF, "Duration", vbTextCompare) Then
        Call ImportCCMData
    End If

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Jeffrey Mahoney]

The code you have looks good. The information I don't have is what is going on inside those 4 SUBS (ImportPData, ImportCCPData, ImportCCIData, and ImportCCMData). How are you providing the path and filename to those subs for importation?

You could send the PathFile like:
ImportPData(FP & FF)
.....
... And on the receiving end the SUB could look like
Sub ImportPData(PathFile as string)
Code to open file and import the data based on PathFile
.....
End SUB

So When you say: "Data is importing, but it appears that the same file is being imported multiple times. Example, if the following files are in the target folder, it appears only 1 of the files is being imported multiple times."
I need to know how those subs are opening workbooks to import.

 

[reberryjr]

The code you have looks good. The information I don't have is what is going on inside those 4 SUBS (ImportPData, ImportCCPData, ImportCCIData, and ImportCCMData). How are you providing the path and filename to those subs for importation?

You could send the PathFile like:
ImportPData(FP & FF)
.....
... And on the receiving end the SUB could look like
Sub ImportPData(PathFile as string)
Code to open file and import the data based on PathFile
.....
End SUB

So When you say: "Data is importing, but it appears that the same file is being imported multiple times. Example, if the following files are in the target folder, it appears only 1 of the files is being imported multiple times."
I need to know how those subs are opening workbooks to import.

While they have different snippets within them (some filter data, some only copy specific columns, etc.), here is the general structure.

Sub ImportCCIQRData()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook, s As Workbook
Dim mI As Worksheet, sD As Worksheet
Dim mILR As Long, sDLR As Long

Set m = ThisWorkbook
Set mI = m.Sheets("CC_I")

'Removes filters from the working data if any exist.
If mI.AutoFilterMode Then mI.AutoFilterMode = False

'Unhides any columns and rows that may be hidden on the working data.
With mI.UsedRange
    .Columns.EntireColumn.Hidden = False
    .Rows.EntireRow.Hidden = False
End With

mILR = mI.Range("A" & Rows.Count).End(xlUp).Row

'*****This section allows the User to select individual files for upload.*****
'Prompts the User to select the desired file to import.
'ISelect = MsgBox("Navigate to, and open the most recent Quality report.", vbOKCancel + vbInformation)

'If ISelect = vbOK Then
'    With Application.FileDialog(3)
'        .AllowMultiSelect = False
'        If .Show Then
'            fullpath = .SelectedItems.Item(1)
'            Set s = Workbooks.Open(fullpath)
'        End If
'
'        If s Is Nothing Then Exit Sub
'
'        Set sD = s.Sheets("Risk Responses")
'
'        sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row
'    End With
'Else:  Exit Sub
'End If

'Opens and sets the source file.
Set s = Workbooks.Open("\\Path masked for security\*Quality Report.xlsx")
Set sD = s.Sheets("Risk Responses")
sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row

sD.Activate

'Deletes the first 3 rows as they don't contain pertinent data.
sD.Range("A1:A3").EntireRow.Delete

'Removes filters from the source data if any exist.
If sD.AutoFilterMode Then sD.AutoFilterMode = False

'Unhides any columns and rows that may be hidden on the source data.
With sD.UsedRange
    .Columns.EntireColumn.Hidden = False
    .Rows.EntireRow.Hidden = False
End With

'Copies the data from the source file and pastes it onto this worksheet.
sD.Range("A2:P" & sDLR).Copy
    mI.Range("A" & mILR + 1).PasteSpecial xlPasteValues

'Closed the source file without saving it.
s.Close SaveChanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub