Hello all, I'm attempting to run a macro to open all files in a
directory however, the directory changes from month to month (i.e.
from Apr to May and so on throughout the year. How can I point to the
new directory each month? This is the code I'm using now, but I want
to allow different users to run this monthly and they are not savy on
going in and editing my macro to change the directory path, and I'm
happy that thay can't. Appreciate any assistance with this macro.
Thank you, Ron

Sub Open_Files_In_A_Directory()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim i As Integer
'define the directory to be searched for files
fPath = "T:\Budget Reports\NAPO\National Parts Operations\01 Apr\"


'build a list of the files
fName = Dir(fPath & "*.xls")
While fName < ""
'add fname to the list
i = i + 1
ReDim Preserve fileList(1 To i)
fileList(i) = fName
'get next filename
fName = Dir()
Wend


'see if any files were found
If i = 0 Then
MsgBox "No files found"
Exit Sub
End If


'cycle through the list and open
'just those with the letter DP in the filename
'instr the following way is a case insensitive test
For i = 1 To UBound(fileList)
If InStr(1, fileList(i), "DP", 1) 0 Then
Workbooks.Open fPath & fileList(i)
End If
Next
End Sub