Microsoft Excel - scripting problem - Asked By Rudi on 23-May-13 12:05 AM

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
Harry Boughen replied to Rudi on 23-May-13 01:24 AM
Hello Rudi,
This is off the top of my head but try:
If fsoFile.Name = CurrentWeek&".xls" Then

Regards
Harry
Rudi replied to Harry Boughen on 23-May-13 08:15 AM
Hi Harry,
Thanks for your reply but that didn't work.
Any other ideas?
Cheers
Rudi
Harry Boughen replied to Rudi on 23-May-13 08:26 AM
Hello Rudi,
Perhaps it is in your construction of the file name.  Try:

CurrentWeek = Format(Date, "yyyy ") & Format(Date - (Weekday(Date, vbMonday)) + 1, " mmm dd") & " - " & Format(Date - (Weekday(Date, vbMonday)) + 7, " mmm dd")

or whatever you need to get the file name correct.
The first suggestion is required as well.
Regards
Harry
Rudi replied to Harry Boughen on 23-May-13 09:16 AM
Hi Harry,

Actually you were right the first time (sort of)
If fsoFile.Name = CurrentWeek&".xls" Then
should be
If fsoFile.Name = CurrentWeek & ".xls" Then
Thanks again. You were a great help.
Cheers
Rudi
Harry Boughen replied to Rudi on 23-May-13 05:15 PM
Sorry Rudi,
I'll have to try to remember to not be so literal next time.
Harry