date formating issue

[Michaelpfreem]

Dear all,

I am having a small formating issue, at least I assume it is a formatting issue.

I have a macro that opens a file each day.

The file name will change each month for example

filename 2011 Apr endoffilename.xls

Next month it will be

filename 2011 May endoffilename.xls

Below is the section of the macro that sorts out the filename:

OldDate = Now
OldMonth = Month(OldDate)
OldYear = Year(OldDate)
If Len(OldMonth) < 2 Then OldMonth = "0" & OldMonth
NewMonth = Format(OldMonth, "mmm")

The year works fine, but the month doesn't.

It finds the OldMonth as "4", I then add a zero so the OldMonth is "04", but I need it to turn that to a text, so in this case the "04" would become NewMonth = "Apr". But instead when it changes it to the three diget version it just becomes "Jan".

Can anyone tell me what I am doing wrong?

Many thanks in advance,
Mike

 

[Richard Schollar]

Hi Mike

If the Year and Month portion is purely dependent on the current date (I wasn't clear if this was the case) then you could just use Format to generate your string:

NewFilename = "filename " & Format(Date,"yyyy mmm ") & "endoffilename.xls"

 

[jim may]

Sub Foo()
OldDate = Now[COLOR="Red"]()[/COLOR]
OldMonth = Month(OldDate)
OldYear = Year(OldDate)
If Len(OldMonth) < 2 Then OldMonth = "0" & OldMonth
NewMonth = Format(DateSerial(OldYear, OldMonth + 0, 1), "mmm")
MsgBox "Your New Month is " & NewMonth
End Sub

 

[Michaelpfreem]

Thanks Richard that did the trick with a lot less code

Thanks,
Mike