Error in Picking the specific excel file

[bvsramesh]

Hi Team,

I wrote a macro to open a excel workbook from a specific path and there i have used few cases to open the workbook.

[vba]Sub PreviousDay_BVSR_FIle_and_Open()

Application.ScreenUpdating = False

Dim Filename As String

Select Case Weekday(Now) 'Sunday is day(1)
Case 1 'if Saturday, look for Friday's File
Filename = "D:\Cash Reports\" & Format(Date, "yyyy\\mmmm\\") & "BVSR Cash " & Format(Now() - 2, "dd.mm.yyyy") & ".xl*"

Case 2 'if Sunday or Monday, look for Friday's File
Filename = "D:\Cash Reports\" & Format(Date, "yyyy\\mmmm\\") & "BVSR Cash " & Format(Now() - 3, "dd.mm.yyyy") & ".xl*"

Case 3, 4, 5, 6, 7 'if day(3 thru 7), look for yesterdays File
Filename = "D:\Cash Reports\" & Format(Date, "yyyy\\mmmm\\") & "BVSR Cash " & Format(Now() - 1, "dd.mm.yyyy") & ".xl*"

End Select

On Error Goto errorHandler

Workbooks.Open Filename

Exit Sub

errorHandler:
MsgBox "File not found"

End Sub [/vba]
The code is not picking the previous month's file.

for eg: if today is 01.06.2012 (Friday) code needs to pick the file from previous day's file in the previous months folder (i.e."D:\Cash Reports\2012\May\BVSR Cash 31.05.2012.xlsx (Thursday))

Please make the necessary changes to the above code.

Thanks in Advance,

Regards,

SiriSurya

[Simon Lloyd]

Have you checked to see if the workbook exists? you might have a typo, do the following[vba]Msgbox "Checking for " & "D:\Cash Reports\" & Format(Date, "yyyy\\mmmm\\") & "BVSR Cash " & Format(Now() - 1, "dd.mm.yyyy") & ".xlsx"
If Dir("D:\Cash Reports\" & Format(Date, "yyyy\\mmmm\\") & "BVSR Cash " & Format(Now() - 1, "dd.mm.yyyy") .xlsx") <> "" Then
msgbox "File exists"
else
msgbox "File does not exist"
End if
[/vba]
Personally i think you'll find your filename wont exist as you are looking for yyyy\\mmmm\\ and it should be yyyy\mmmm\

[Simon Lloyd]

Please read the link in my signature with regards to cross posting!

Crossposted here: http://www.thecodecage.com/forumz/mi...nths-file.html