Save a file as previous months date

[dburks1]

I am trying to create a script that will allow for me to to save a file as the previous month and also display the corresponding year. The script that I have below is getting me close to what I want but its saving the file as the current month. I know that what I have below isnt going to yield me the results that I am looking for but this is where i get stuck.

I would like for it to read as follows: Student Report 03-2010

TempFilePath = Application.DefaultFilePath
TempFileName = "Student Report" & Format(Now, "mm-yyyy")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.Close SaveChanges:=False
End With

 

[SkipVought]

hi,

  dim dte as date
  dte = DateSerial(Year(Date), Month(Date),0)
    TempFileName = "Student Report" & Format(dte, "mm-yyyy")

I would also recommend, (the voice of experience) to format as "yyyy-mm" for the purposes of SORTING and locating files easily.

Otherwise your file sequence will be scrambled!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Format(DateSerial(Year(Now),Month(Now),0), "mm-yyyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

Have a look at the Month function:
You seem to want Month(now())-1.

There is also a Year function.

DateSerial Function:
DateSerial(year, month, day)





Gavin

 

[Gavona]

I think I understand that if Now() is January 2010 then you want to use 2009_12 as part of the filename.

myMonth=Month(Now())
myYear=Year(Now())

If myMonth=1
  mymonth=12
  myYear=MyYear-1
Else
  myMonth=myMonth-1
EndIf

TempFileName = "Student Report" & Format(DateSerial(myYear, myMonth,0), "mm-yyyy")

Gavin

 

[SkipVought]

Gavin,

You do not need to do all that extra logic.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dburks1]

Thanks...I got the report to save accordingly. How would I go by adding a space between the report name and date. As of now they are merged together.


TempFileName = "Student Report" & Format(DateSerial(Year(Now),Month(Now),0), "mm-yyyy")

Which reads as: Report03-2010

Thanks!

 

[PHV]

TempFileName = "Student Report " & Format(DateSerial(Year(Now),Month(Now),0), "mm-yyyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dburks1]

Awsome...thanks for the help.

 

[Gavona]

Thanks Skip, I was a bit slow picking that up from your (and PHV's) posts.

Gavin