Excel macro question--renaming with the current date?

Factual Questions
1

I have this Excel macro that opens a template, imports a text file into it, makes a bunch of changes and then saves the file with a different filename. Problem is I need to make this as goof-proof as possible for the person using it, and the file name it renames to right now is static, so every time they run the macro, it will overwrite the existing file. What I want it to do is name the file something like “file_Apr_2004” or something like that–in other words, I need the current month and year to be part of the filename when it runs. Is there a way to do this?

2

I don’t mean to insult you, but I don’t know anything about your system setup or your Excel skills. My apologies if these questions seem stupid.

What version of Excel are you using? Are you using VBA in your macro? Do you know how to edit your macro?

GorillaGirl

3

I am using Excel 2000…I did not write the macro in VBA, I just used the macro recorder but I do have some limited experience editing the macros. Currently this is what it looks like when I step into the macro:
Sub rename()
’
’ rename Macro
’ Macro recorded 4/28/2004 by bbudd00
’

’
ChDir “C:\Documents and Settings\bbudd00\Desktop”
ActiveWorkbook.SaveAs Filename:= _
“C:\Documents and Settings\bbudd00\Desktop
ewfile.xls”, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

4

Um, you could try something like this.

ActiveWorkbook.SaveAs FileName:= _
    "yourpathhere" & (Format(Date, "mmmm_yyyy")), FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

Dunno if that’s what you’re looking for.

5

Just when you pick the filename you want for your SaveAs make it something like

“filename_” & Month(Date) & Day(Date) & Year(Date) & “.xls”

6

Sorry, screwed up the name, and I should elaborate.

“c:\subdirectory\file_” & (Format(Date, “mmmm_yyyy”)) & “.xls”

This will evaluate to “c:\subdirectory\file_april_2004.xls”.

Hope this helps.

7

thanks, Gangster Octopus , that’s exactly what I needed!

8

Yeah, do what Gangster Octopus and Tabby Cat said.

Jeez, you leave the thread for a little while and look what happens… :wink:

9

Tabby Cat’s solution is superior to my solution, becuase it gives you more flexibility on how you want the date to appear.