Saving with timestamp in actual Filename

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
I an trying to save a file with a timestamp in the filname name so that it doesn't save over any previous Files in that folder with the same filename. does anyone know the VB code for that.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
L

Legacy 98055

Guest
Every time that you save or only when the workbook is closed. Ie, the final save...
 

help_questions

Board Regular
Joined
Aug 22, 2005
Messages
215
ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
"C:\report_" & Format(Now, "mm-dd-yy, hmm AM/PM") & ".htm" _
, "AGENT", "", xlHtmlStatic, "report_10190", "").Publish (True)

The above code date/time stamps a web page.....so you'll need to modify this for xls,,,
 

Desert Piranha

New Member
Joined
Nov 15, 2005
Messages
1

ADVERTISEMENT

either one is the same......if its in the end or the middle
Hi David,

This help? Uses the same workbook name and path.

Code:
Sub SaveAsToVarVar()
    Application.ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & _
    Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & " " & Format(Now, _
    "mm-dd-yy_hh-mm") & ".xls"
End Sub
Dave
 
L

Legacy 98055

Guest
This is a snippet taken from a personal addin I named "Project Keeper" and is along the same lines as other replies to this thread. When you create a new workbook and save it for the first time, it simply retains the name given to it. For example, "Book1.xls". All subsequent saves will tag the name with a date and time stamp such as "Book1.092406094329.xls".

<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_BeforeSave(ByVal SaveAsUI <font color="#0000A0">As</font> Boolean, Cancel <font color="#0000A0">As</font> Boolean)

       Application.EnableEvents = <font color="#0000A0">False</font>
       Cancel = <font color="#0000A0">True</font>

       <font color="#0000A0">If</font> Me.FullName = Me.Name <font color="#0000A0">Then</font>
           Application.Dialogs(xlDialogSaveWorkbook).Show
       <font color="#0000A0">Else</font>
           <font color="#0000A0">Dim</font> NewWbName <font color="#0000A0">As</font> <font color="#0000A0">String</font>
           NewWbName = Left(Me.Name, InStr(Me.Name, ".")) _
                           & Format(Now, "MMDDYYHHMMSS.") _
                           & Right(Me.Name, 3)
           Me.SaveAs Replace(Me.FullName, Me.Name, NewWbName)
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>

       Application.EnableEvents = <font color="#0000A0">True</font>

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("925200603912980").value=document.all("925200603912980").value.replace(/<br \/>\s\s/g,"");document.all("925200603912980").value=document.all("925200603912980").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("925200603912980").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="925200603912980" wrap="virtual">
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.EnableEvents = False
Cancel = True

If Me.FullName = Me.Name Then
Application.Dialogs(xlDialogSaveWorkbook).Show
Else
Dim NewWbName As String
NewWbName = Left(Me.Name, InStr(Me.Name, ".")) _
& Format(Now, "MMDDYYHHMMSS.") _
& Right(Me.Name, 3)
Me.SaveAs Replace(Me.FullName, Me.Name, NewWbName)
End If

Application.EnableEvents = True

End Sub</textarea>
 

Forum statistics

Threads
1,137,208
Messages
5,680,196
Members
419,889
Latest member
ballsofspartans

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
Top