Hello,
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.
Sub DoCurrentWeek()
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
wb.Close SaveChanges:=True
End If
Next
Next
End Sub