Retrieve saved workbook according to date

[Jackblack1]

Hi All

I have a macro that saves a workbook according to yesterdays date automatically when a button is clicked. The problem is this macro does not exclude weekend dates. I need it exclude weekend dates so that on Monday it is save according to Fridays date.(see macro below)

Then I am trying to create a macro that will go back and retrieve the prior dates saved workbook, this happens each day. One important thing, if it monday then it will retrieve fridays. How can this be done?

sPath = "R:\FACSAPPS\FA\CUS\STL\DIRECT PROGRAM\Exemptive Order\2007\"

If Dir(sPath & Format(Date, "mmm yy"), vbDirectory) = "" Then

VBA.MkDir (sPath & Format(Date, "mmm yy"))
End If

sPath = sPath & Format(Date, "mmm yy") & "\"

sFileName = Format(Now(), "mmddyy") & " -exe.xls"

ActiveWorkbook.SaveAs Filename:=sPath & sFileName, FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sorry for the confusion but it is the best I could do explaining this.

Thanks

 

[Kenneth Hobson]

You may need to consider Holidays as well.

You can change the date format to suit.

Sub Test()
  Dim thisDate As Date
  'thisDate = Date
  thisDate = #7/16/2007#
  MsgBox "Todays Date: " & Format(thisDate, "mm/dd/yyyy") & vbCrLf & _
    "Minus 1 day but more if on weekend: " & M1OnWeekday(thisDate)
End Sub


Function M1OnWeekday(d As Date, Optional dateFormat As String = "mm/dd/yy") As String
  Dim m1Date As Date
  m1Date = d - 1
  Do Until Not (Weekday(m1Date) = 1 Or Weekday(m1Date) = 7)
    m1Date = m1Date - 1
  Loop
  M1OnWeekday = Format(m1Date, dateFormat)
End Function

 

[Jackblack1]

Thanks for the reply,

I sorry for not being clear, but the macro is save the report according to a prior (not yesterdays) day. When I click the button it saves it in a folder accoridng to the prior day as the name. I want it to save the prior days date as the name but when saved on monday it saves as fridays date.

The other part of the macro is when I click the button it will open the prior dates report thats name is the prior days date. It will open the report extract some information and close.

I don't know how to program it so that excel will read the name know it is the prior days and open it.

thanks

 

[Kenneth Hobson]

Then all you have to do is change M1Date=d+1 in the function to M1Date=d.

Then call the function to build the filename something like:

sFileName = M1Weekday(d, "mmddyy") & " -exe.xls"

Where d is your prior date however you determined that.