bvsramesh
06-13-2012, 07:08 AM
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.
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
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
I wrote a macro to open a excel workbook from a specific path and there i have used few cases to open the workbook.
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
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