Excel Macro -Dynamic File Save Name

August 30, 2007 at 14:12:31
Specs: XP Prof, 2.8 / 512

We need to save a copy of a daily report as both its template name (say Sales) and some kind of daily archive name (pref like Sales_Aug23) - is there VBA code that will do that automatically (I can assign the macro to a button in the spreadsheet.)

See More: Excel Macro -Dynamic File Save Name

Report •


#1
August 30, 2007 at 17:20:44

Either version of this code should work.

The first one builds the filename in pieces so you can see how it works.

The second just does it all in one line.

Keep in mind that the word wrap in this forum makes code hard to read. Copy the code into the VBA Editor and it should format itself correctly.

Sub SaveByDate()
MyMonth = Left(MonthName(Month(Date)), 3)
MyDay = Day(Date)
MyFileName = "Sales_" & MyMonth & MyDay
ActiveWorkbook.SaveAs Filename:=MyFileName
End Sub

Sub SaveByDate2()
ActiveWorkbook.SaveAs Filename:= "Sales_" & Left(MonthName(Month(Date)), 3) & Day(Date)
End Sub


Report •

#2
August 30, 2007 at 17:42:20

BTW - if you want to save the file multiple times on the same day and not have to answer Yes to the File Already Exists dialog box, add

Application.DisplayAlerts = False

as the first line of code and

Application.DisplayAlerts = True

as the last.

This will save the file over the top of the existing one without asking the user.



Report •

#3
August 31, 2007 at 05:47:20


DerbyDad:

Awesome, works great. THANKS!

Now, (don't you love the now.... stuff?)

Can I make the base file name variable (so I can just have one macro in my Personal.xls) that covers file names like Cost and returns - it would simply take the current file name and append the date.

One problem would of course be the multiple executions (it woudl probably result in Cost_Aug31_Aug31_Aug31 etc), but I think we could be smart enough not to run the macro on an already archived file.

Grateful for your assistance.


Report •

Related Solutions

#4
August 31, 2007 at 07:10:35

There are many, many ways to accomplish this "now" request.

If the first part of the filename that you are looking for is in a cell in the worksheet, the macro could read that cell and build the filename. For example if A1 contained the word Sales, this should work:

MyFileName = Range("A1") & "_" & MyMonth & MyDay

Or you could have VBA grab the first part of the actual filename, up to the underscore, as follows:

MyFileName = Left(ActiveWorkbook.Name, WorksheetFunction.Find("_", ActiveWorkbook.Name)) & MyMonth & MyDay

Or you could have VBA present an InputBox asking the user for the first part of the filename, but that would take away some of the automation.

None of this would extend the filename like you were afraid would happen.



Report •

#5
September 4, 2007 at 01:01:16

Of course, DerbyDad03's VBA code assumes your file name already has a "_" in it. Something like this may be a bit more appropriate:

Sub SaveMe()
Dim someName As String
If InStr(ThisWorkbook.Name, ".") Then
someName = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1)
Else
someName = ThisWorkbook.Name
End If
If InStr(someName, "_") Then _
someName = Left(someName, InStr(someName, "_") - 1)

someName = someName & "_" & MonthName(Month(Now), True) & Day(Now)
ThisWorkbook.SaveAs someName
End Sub


Report •

#6
September 4, 2007 at 05:34:48


When I run this macro it seems to be "assuming" it is running from the workbook Personal.xls (where I store my general macros of course to be run anytime on any spreadsheet..) therefore it is trying to save it as personal_sep1.xls - how do I make it apply to the current work book? The one I used from DerbyDad did not do this - so maybe I did something wrong in the cut/paste function.
(It is strange by the way when I first go to macro editor this module does not appear with the two first modules I have in personal.xls, I have to unhide Personal.xls first to get module3 to appear!)

Here is what I pasted:

=================================
Sub SaveByDate()


Dim someName As String
If InStr(ThisWorkbook.Name, ".") Then
someName = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1)
Else
someName = ThisWorkbook.Name
End If
If InStr(someName, "_") Then _
someName = Left(someName, InStr(someName, "_") - 1)
someName = someName & "_" & MonthName(Month(Now), True) & Day(Now)
ThisWorkbook.SaveAs someName


End Sub

=======================================



Report •

#7
September 4, 2007 at 06:59:06

Razor2.3,

I'm not bickering here - OK, maybe I am ;-) - but I didn't really "assume" there was an _ in the file name. I simply went along with the example markrohde used in his original post - Sales_Aug23.

It seems to me that if the code is going to be used to create daily archives of files whose base name never changes, it would be rather inefficient to keep checking for the underscore every day.


Report •

#8
September 4, 2007 at 17:42:12

<bDerbyDad03: Well, that depends on if he's using a generic template, or just modifying the same document each day. It'd also require the template to have an "_" included.

markrohde: It works on my systems, but my work and home PCs all have 2K installed. I usually use ThisWorkbook instead of ActiveWorkbook, but that'd require the macro to be a part of said Workbook. You can try changing it to ActiveWorkbook, and the macro will run on whatever workbook you happen to have selected at the time.


Report •


Ask Question