changing paer of the file name with VBasic during save as

[grahamiwa]

I have a spreadsheet that I want to change part of the file name to todays date using VBasic during the Save As part of a macro

Can I do it and how?

 

[GTO]

Yes. Show us the code you have so far.

 

[grahamiwa]

This is the save as I will use to save the file to a different folder


ActiveWorkbook.SaveAs Filename:= _<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
"\\MLBDAT02\GCARNCR$\Data\ePTW Schedule Master 6 Mar 2011.xls", FileFormat:= _<o></o>
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _<o></o>
, CreateBackup:=False

As have neither used VBasic nor got great skills I am lost on how to change the date to todays date as part of the macro. All the other things I need the macro to do I have figured out except this ending

 

[grahamiwa]

Sorry I forgot to mention I am doing this at work and we are only using Excel 2003 the upgrade to Excel 2010 is planned to happen within the next 2 months

 

[GTO]

Try:

Option Explicit
    
Sub exa()
Dim strFileName As String
    
    strFileName = "\\MLBDAT02\GCARNCR$\Data\ePTW Schedule Master" & Chr(32) & _
                  Format(Date, "d mmm yyyy") & ".xls"
    MsgBox strFileName
End Sub