Update Directory Path in Code

[Ron]

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

 

[Rick Rothstein]

If they will only run you macro in the month that is supposed to be looked
up, you could do this...

CurrentMonthAbbreviation = Format(Now, "mmm")
fPath = "T:\Budget Reports\NAPO\National Parts Operations\01 " & _
CurrentMonthAbbreviation & "\"

Otherwise, you will need to ask the user what month they want....

CurrentMonthAbbreviation = InputBox("What month abbreviation?")
fPath = "T:\Budget Reports\NAPO\National Parts Operations\01 " & _
Left(CurrentMonthAbbreviation, 3) & "\"

Although you will probably need to use some kind of validation to make sure
the user actually entered a month abbreviation.

 

[Ron]

Hi Rick, thank you for your assistance. Your solution fit my project
perfectly. I ended up using the second solution. I'm having a
problem with setting CurrentMonthAbbreviation as a variable if, I have
Option Explicit in play. This is what I'm using.

dim CurrentMonthAbbreviation as string
Set CurrentMonthAbbreviation = CurrentMonthAbbreviation =
InputBox("What month abbreviation?")

I get the variable not defined error.
So, I' not using Option Explicit at the moment. I think I'm okay with
this due to it's only used in our inviroment. Appreciate your
thoughts on this.

Thank you, Ron

 

[Rick Rothstein]

I'm not entirely sure what that line with two equal signs is trying to do.
Dim'ming CurrentMonthAbbreviation as String is fine, but you do not need a
Set statement to assign anything to it (Set is use to set a reference for an
Object), just make the assignment the way I showed it in my code...

Dim CurrentMonthAbbreviation As String
.....
.....
CurrentMonthAbbreviation = InputBox("What month abbreviation?")
fPath = "T:\Budget Reports\NAPO\National Parts Operations\01 " & _
Left(CurrentMonthAbbreviation, 3) & "\"
.....
.....

Oh, and I encourage you to use Option Explicit... it will help protect you
against mistyping a variable name during coding.

 

[Ron]

Hi Rick, thank you again. Your solution worked out great. I added
back Options Explicit and dim'd the variable and things worked as
planned. Also, thanks for straighting me out on setting variables.
Thank you, Ron