Date: End of last month as YYMM

[Guest]

Hello,
I am looking for a good way to capture the YYMM of the last month as a
string.
My macro runs a monthly process and I would like it to automatically save to
monthly directory labeled by the year and month. If I were to run it today,
24-May-2007 I would like the result to be YYMM = "0704".

Could someone suggest a way to do this in VBA without relying on computing
on a worksheet?

Thanks in advance,

 

[Rick Rothstein \(MVP - VB\)]

I am looking for a good way to capture the YYMM of the last month

as a string.
My macro runs a monthly process and I would like it to automatically
save to monthly directory labeled by the year and month. If I were to
run it today, 24-May-2007 I would like the result to be YYMM = "0704".

Did you really mean the "last month" as in the one previous May? Assuming
you did...

YourMonth = #24-May-2007#
PreviousMonth = Format$(DateAdd("m",-1,YourDate), "yymm")

Rick

 

[Bob Phillips]

You get that date like so

Format(Date - Day(Date),"YYMM")

to automatically create the directory use

MkDir Format(Date - Day(Date),"YYMM")

but wrap error handling around it in case it already exists

On Error Resume Next
MkDir Format(Date - Day(Date),"YYMM")
On error Goto 0


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)