Using last business date to save

Posted by dan on October 25, 2001 10:12 AM

I am using the following code to attempt and save a file using the previous business day. The code I have below does not take into account weekends/holidays etc. I need to either have a business day counter(hard), or I can use the trade date which is listed on the sheet "Summary" ("b3"). How do I reference the date in cell ("b3") and then add it in place of the SaveAsFile =_ Application.Text(Now() - 1, "mm_dd ") below?

Thank you,
Dan

Private Sub ActualsButton_Click()
Dim SendPath, NameFile, SaveAsFile
Dim strZ As String
strZ = "PSF Trade Actuals"
On Error GoTo errorhandler
SendPath = "C:\"
NameFile = strZ
SaveAsFile = Application.Text(Now() - 1, "mm_dd ")
NameFile = SaveAsFile & NameFile & ".xls"

ChDir SendPath
ActiveWorkbook.SaveAs Filename:=SendPath & NameFile, _
FileFormat:=xlExcel9795, CreateBackup:=False
Unload Me
SaveAs = True
Exit Sub
errorhandler:
MsgBox "Your work has not been saved", vbCritical
End Sub

Posted by Dan on October 25, 2001 10:46 AM

I'm no VB expert, but...
Couldn't you replace the SaveAsFile line to:

SaveAsFile=Sheets("Summary").Cells.Range("B3").Value

HTH.

Posted by Tom Urtis on October 25, 2001 11:13 AM

Here's a suggestion

dan,

Only a suggestion, but here is a code that I think gets close to what you are looking for, in a more succinct fashion.

Sub YesterdaysNews()
Dim inc As Date
Application.DisplayAlerts = False
If Weekday(Now(), 2) = 1 Then
inc = Weekday(Now(), 2) - 3
Else
inc = -1
End If
ActiveWorkbook.SaveAs ("C:\YourFolderName\" & Format((Now() + inc), "mm""-""dd") & " PSF Trade Actuals.xls")
Application.DisplayAlerts = True
End Sub

This is a start, in that it allows for weekends but not your company's holidays (you'd need to set up and specify a range for that and plug it into the code). My goal here is to first see if this is what you want while using less code lines, and then maybe you can take it from here.

HTH

Tom Urtis

Posted by Travis on October 25, 2001 4:26 PM

This will save the file in the directory C:\PSF Trade Actuals\ and name it what is in cell B3. I think that is what you are asking...

Dim rngeFileName As Range, sPath As String
Set rngeFileName = Range("B3")
sPath = "C:\PSF Trade Actuals\"
ActiveWorkbook.SaveAs sPath & rngeFileName.Value