Mat,

The behaviour is a result of changing using the Day, Month, Year  functions.
Month(Now) returns an integer value "1" you can't format an integer value
with a Date format definition.  Similiarily Day(Now) returns an integer
value "30."  You are happy now, but on Tuesday you will have problems with
it also :-).  Notice also the Year(Now) is not giving you the result you
want.  You want 05 and you are getting 2005.  Again, Year(Now) returns a
integer value "2005."  You can't format it with a date format definition,
because it isn't a date anymore.  Look at the following and you will see how
you could build you string as individual components of the date also note
that you don't need to build it as individual components:

Sub Test()

StrDay = Format(Day(Now), "0#")
strMonth = Format(Month(Now), "0#")
strYear = Right(Year(Now), 2)
MsgBox "EN" & StrDay & strMonth & strYear & ".txt"

strDate = Format(Date, "ddmmyy")
MsgBox "EN" & strDate & ".txt"

End Sub

Hi Mat,

if it has to be, then like this:

MsgBox Format(Date, "YYYYMMDD")

which is the basic format of ISO 8601.
And I'd strongly advice you, not to use
any other kind of formatting, like mm, dd, yy etc...

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

Hi Mat,

You have two errors in each assignment, plus a bonus error in the
month. :-)

First, if you're using the format strings like dd in the Format
statement, you should not be using the Day(), Month(), and Year()
functions. Just use Now as the starting expression in Format.

Second, the format strings (the second argument in each Format call)
must be enclosed in quotes. Otherwise, VBA assumes that they're
undeclared variables with a default data type of Variant and a default
value of 0. I strongly suggest using the Option Explicit setting,
which would have told you about the undeclared variables (see
http://www.word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm for an
explanation).

As the bonus, when you format the month, the mm must be upper case MM.
The lower case mm would give you the minutes part of Now, not the
month.

So with all the corrections, your code should be

Dim strDay As String, strMonth As String, strYear As String
strDay = Format(Now, "dd")
strMonth = Format(Now, "MM")
strYear = Format(Now, "yy")

One more thing: I don't like your naming scheme because it sorts
poorly. A file created on 29 January 2005 would appear in a sorted
list after a file created four days later on 02 February 2005. The
better scheme would be to reverse the order of the parts to yyMMdd.
And if there's any chance you'd have to deal with files created before
2000, you should use all four digits of the year.

--
Regards,
Jay Freedman
Microsoft Word MVP         FAQ: http://word.mvps.org