PDA

View Full Version : Saving a file with previous day date in specific location



Klartigue
02-19-2015, 11:08 AM
Right now, I use this code to save a file. In the input box I put the format 2.18.2015 for example. However, it never gets saved in the correct location so I am not sure if I need to tweek the macro/not sure if there is a reason it won't save in the correct location.

I don't know if there is an automatic default where I can say save as OTHER pricing (2.18.2015).xlsx ? Date is previous days business date, also the date in cell A1 so I don't know if there is a way to chanel that data in the cell when saving? I just need to save with the previous day's date and have it save in the correct folder but right now with the input box it won't save in the correct location everytime.



Sub SaveDebitreport1()
TodaysDate = Application.InputBox("Enter Today's Date for File Name", "Today's Date")
ChDir "G:\Fixed Income\Cash and Debit reports"

ActiveWorkbook.Saveas FileName:="Debit report" + " " + "(" + TodaysDate + ")" + ".xls", _
FileFormat:=xlExcel8, CreateBackup:=False

End Sub

Klartigue
02-19-2015, 11:10 AM
Input box is previous day date. And it should be .xls as in the code


Sub SaveDebitreport1()
TodaysDate = Application.InputBox("Enter Today's Date for File Name", "PreviousBusinessDaydate")
ChDir "G:\Fixed Income\Cash and Debit reports"

ActiveWorkbook.Saveas FileName:="Debit report" + " " + "(" + YPreviousBusinessDaydate+ ")" + ".xls", _
FileFormat:=xlExcel8, CreateBackup:=False

End Sub

ZVI
02-19-2015, 04:11 PM
Try this:


Sub SaveDebitReport2()

Const MyFolder = "G:\Fixed Income\Cash and Debit reports\" ' <-- change to suit
Const xlExcel8 = 56
Dim ReportDate, d

' Trap errors
On Error GoTo exit_

' Calc previous workdate
d = Date
If Weekday(d) = vbMonday Then d = d - 3 Else d = d - 1

' Edit previous workdate
ReportDate = InputBox("Enter Date for the File Name in format MM.DD.YYYY", _
"Today - " & Format(Date, "mm.dd.yyyy") & IIf(Weekday(Date) = vbMonday, " Monday", ""), _
Format(d, "mm.dd.yyyy"))
' Validate the date
If Not IsDate(ReportDate) Then
MsgBox "File has not been saved", vbInformation, "Exit"
Exit Sub
End If

' Save
ActiveWorkbook.SaveAs MyFolder & "Debit report (" & ReportDate & ").xls", _
FileFormat:=IIf(Val(Application.Version) > 11, xlExcel8, xlWorkbookNormal), _
CreateBackup:=False

exit_:

' Inform about result
If Err Then
MsgBox Err.Description, vbCritical, "Error #" & Err.Number
Else
MsgBox "Ok", vbInformation
End If

End Sub

Klartigue
02-20-2015, 11:00 AM
I tried this but it keeps highlighting the word "Format" and saying ambiguous name detected?



' Edit previous workdate
ReportDate = InputBox("Enter Date for the File Name in format MM.DD.YYYY", _
"Today - " & Format(Date, "mm.dd.yyyy") & IIf(Weekday(Date) = vbMonday, " Monday",

GTO
02-20-2015, 01:03 PM
A stab in the dark, but have you named a variable or constant "Format"? Or maybe a procedure named Format?

I did not receive the error you did, but for my locale, it would not recognize "02.19.2015" as a date. I changed the dots/full stops to hyphens and it worked.

Mark