File Name built in a Macro

counterscc

Board Regular
Joined
Mar 6, 2006
Messages
119
Hello - I run a macro daily and I end up saving the file the same thing everyday but with that days date at the end of the file name. Is there a way to build this into a macro? Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I could but the code is quite lengthy. But ultimately the macro opens a file named AR Return Credits 1.xls and does a ton of clean up and a couple vlookups and at the end I have the macro save the file but I would love to find a way to have the macro save the file as AR Return Credits 1 - Current Days Date.xls
 
Upvote 0
How's this:

ActiveWorkbook.Path & "\" & "AR Return Credits 1 - " & Format(Date, "mm-dd-yy") & ".xls"

HTH,
 
Upvote 0
Thanks for the help. I may be doing something wrong but when I put that code in the macro I get an error message that says : Complie Error: Expected: Expression. what am i doing wrong. Thanks
 
Upvote 0
Hi Counterscc
I would guess you currently have code that that emulates Ctrl + s to save the file, so incorporating Smitty's suggestion into that is possibly where the error comes from, your save code should look something like:-

Code:
ActiveWorkbook.SaveAs Filename:= _
        ActiveWorkbook.Path & "\" & "AR Return Credits 1 - " & Format(Date, "mm-dd-yy") & ".xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

As always, try this on a COPY of your file.

Also please note that after adding the code to your main file, you will need to save it normally before you run the macro. By default when you use "save as", the original document is closed without saving, all changes are saved to the new copy only.

Hope this helps
 
Upvote 0
Thanks for your help Jason. I think I may be doing this incorrectly. What I did was I opened a new file and saved it on my desktop as Book1.xls. I then created a new macro and pasted in the code from your post and then ran the macro in hopes that it would save another copy of my file as AR Return Credits 1 - 03-23-09.xls but I got another error message.

Run-Time error '1004':
Method 'SaveAs' of object '_Workbook' failed

Is there something wrong with the way I am testing it? Thanks again for your help on this. I greatly appreciate it
 
Upvote 0
After playing around a little bit this code works perfect. The code below saves it to my desktop but I could change it to anywhere that I would like. Thanks for your help!!!!

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\l437729\Desktop\AR Credits Report " & Format(Date, "mm-dd-yy") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
 
Upvote 0
I'm not quite sure why it's giving you the error, that code worked for me.

The only problem you might find is if you try to save to a folder that doesn't exist, I could be wrong but I think the code will error out in that situation, but with the activeworkbook.path line it should have saved it to the same folder as the original that you run the macro from.

Thanks for your help Jason. I think I may be doing this incorrectly. What I did was I opened a new file and saved it on my desktop as Book1.xls. I then created a new macro and pasted in the code from your post and then ran the macro in hopes that it would save another copy of my file as AR Return Credits 1 - 03-23-09.xls but I got another error message.

Run-Time error '1004':
Method 'SaveAs' of object '_Workbook' failed

Is there something wrong with the way I am testing it? Thanks again for your help on this. I greatly appreciate it
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top