# Excel macro question--renaming with the current date?

**URL:** <https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527>\
**Category:** Factual Questions\
**Created:** [April 28, 2004, 5:52pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527 "2004-04-28T17:52:59Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [April 28, 2004, 5:52pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/1 "2004-04-28T17:52:59Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![GorillaGirl](https://avatars.discourse-cdn.com/v4/letter/g/8491ac/32.png) [@GorillaGirl](https://boards.straightdope.com/u/GorillaGirl)\
**Post date:** [April 28, 2004, 7:22pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/2 "2004-04-28T19:22:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [April 28, 2004, 7:38pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/3 "2004-04-28T19:38:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Tabby\_Cat](https://avatars.discourse-cdn.com/v4/letter/t/6a8cbe/32.png) [@Tabby\_Cat](https://boards.straightdope.com/u/Tabby_Cat)\
**Post date:** [April 28, 2004, 7:39pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/4 "2004-04-28T19:39:22Z")

</div>

> [@batsto](#):
>
> 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?

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.

---

<div class="post-metadata">

**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\
**Post date:** [April 28, 2004, 7:41pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/5 "2004-04-28T19:41:00Z")

</div>

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

“filename\_” & Month(Date) & Day(Date) & Year(Date) & “.xls”

---

<div class="post-metadata">

**Author:** ![Tabby\_Cat](https://avatars.discourse-cdn.com/v4/letter/t/6a8cbe/32.png) [@Tabby\_Cat](https://boards.straightdope.com/u/Tabby_Cat)\
**Post date:** [April 28, 2004, 7:42pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/6 "2004-04-28T19:42:38Z")

</div>

> [@Tabby\_Cat](#):
>
> Um, you could try something like this.
> 
> ```
> ActiveWorkbook.SaveAs FileName:= _
> "yourpathhere" & (Format(Date, "mmmm_yyyy")) & ".xls", FileFormat:= _
> xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
> , CreateBackup:=False
> 
> ```
> 
> Dunno if that’s what you’re looking for.

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.

---

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [April 28, 2004, 7:56pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/7 "2004-04-28T19:56:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![GorillaGirl](https://avatars.discourse-cdn.com/v4/letter/g/8491ac/32.png) [@GorillaGirl](https://boards.straightdope.com/u/GorillaGirl)\
**Post date:** [April 28, 2004, 9:08pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/8 "2004-04-28T21:08:15Z")

</div>

Yeah, do what **Gangster Octopus** and **Tabby Cat** said.

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

---

<div class="post-metadata">

**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\
**Post date:** [April 28, 2004, 9:15pm UTC](https://boards.straightdope.com/t/excel-macro-question-renaming-with-the-current-date/242527/9 "2004-04-28T21:15:27Z")

</div>

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