I can't get the following code to recognize CurrentWeek. I get Compile error: Invalid qualifier
If I substitute "CurrentWeek.xls" with the actual name of the workbook , e.g "2013 May 20 - May 26.xls" then the code works perfectly.
Please let me know what I am doing wrong. Thanks in advance.
Dim FSO As Object
Dim fsoFile As Object
Dim fsoFol As Object
Dim CurrentWeek As String
Dim wb As Workbook
Const PATH As String = "C:\BEST TAXI\DRIVERS\"
CurrentWeek = Format(Date, "yyyy ") & Format(Date - (Weekday(Date, vbMonday)) + 1, "mmm dd") & " - " & Format(Date - (Weekday(Date, vbMonday)) + 7, "mmm dd")
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each fsoFol In FSO.GetFolder(PATH$).Subfolders
For Each fsoFile In fsoFol.Files
If fsoFile.Name = "CurrentWeek.xls" Then
Set wb = Workbooks.Open(fsoFile.PATH, , True)
' do some stuff