VBA Code amend to show date and time

iwoodland

New Member
Joined
Jan 3, 2017
Messages
6
hi all - thanks for your help in anticipation. I work in IT but don't know VBA at all unfortunately. Wondered if you could help me amend the following code

-----------------------------------------
Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Create_BackUp SourceWB:=Me, Path:="C:\Users\ian.woodland\Dropbox\irw\CMC\CMC Back Ups"

End Sub


Private Sub Create_BackUp(SourceWB As Workbook, Path As String)

Dim oWB As Workbook

On Error Resume Next

Set oWB = Workbooks("BackUp Of " & SourceWB.Name)

If Err = 0 And oWB.Path & _
Application.PathSeparator = Path Then
oWB.Close True
End If

Kill oWB.FullName

If Not SourceWB.Name Like "BackUp*" Then
SourceWB.SaveCopyAs Path & "BackUp " & SourceWB.Name
End If


End Sub
--------------------------------------------------------

This automatically puts a copy of my excel file in a different location when i close my file.

Wondered if someone could amend the code so that the file name includes the date and time as part of the file name. Preferably YYYY-MM-DD HH.MM.

Thanks again in anticipation!

Ian W - Sunny and cold England!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi,
In case your code is working change this line
Code:
SourceWB.SaveCopyAs Path & "BackUp " & SourceWB.Name

to this line:
Code:
SourceWB.SaveCopyAs Path & "BackUp " & SourceWB.Name & " " & Format(Now, "yyyy-mm-dd HH.MM")
 
Upvote 0
Code:
Option Explicit

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Create_BackUp SourceWB:=Me, Path:="C:\Users\ian.woodland\Dropbox\irw\CMC\CMC Back Ups"
 End Sub

 Private Sub Create_BackUp(SourceWB As Workbook, Path As String)
 Dim oWB As Workbook
 Dim Filename As String
 On Error Resume Next
 Set oWB = Workbooks("BackUp Of " & SourceWB.Name)
 If Err = 0 And oWB.Path & Application.PathSeparator = Path Then oWB.Close True

 Kill oWB.FullName
 With SourceWB
    If Not .Name Like "BackUp*" Then
       Filename = Left(.Name, Len(.Name) - 4)
       Filename = Filename & Format(Now, "YYYY-MM-DD HH.MM")
       Filename = Filename & Right(.Name, 4)
       .SaveCopyAs Path & "BackUp " & Filename
    End If
End With
 End Sub
 
Upvote 0
Hi,
In case your code is working change this line
Code:
SourceWB.SaveCopyAs Path & "BackUp " & SourceWB.Name

to this line:
Code:
SourceWB.SaveCopyAs Path & "BackUp " & SourceWB.Name & " " & Format(Now, "yyyy-mm-dd HH.MM")

Kasan

I think you need to strip out the file extension before appending the timestamp.
 
Upvote 0
@njimack - you'r right.
As soon as requirement was "file name includes the date and time as part of the file name" - not necessary to put date/time at the end.
Simplest decision would be
Code:
SourceWB.SaveCopyAs Path & "BackUp " & Format(Now, "yyyy-mm-dd HH.MM") & " " & SourceWB.Name
 
Upvote 0
That's working Great Kasan many thanks - any chance the date and time can go at the end before the .xls?
 
Upvote 0
Also - my lack of knowledge for which i apologise - the instruction has always just overwritten the previous back up - can you amend the coding so that it creates a new copy rather than overwriting the existing back up?

Many thanks for your patience!
 
Upvote 0
Also - my lack of knowledge for which i apologise - the instruction has always just overwritten the previous back up - can you amend the coding so that it creates a new copy rather than overwriting the existing back up?

Many thanks for your patience!

Please ignore this - it is doing it - just need the date and time at the end!
 
Upvote 0
njimack's post should work if its a .xls

Code:
 With SourceWB
    If Not .Name Like "BackUp*" Then
       Filename = Left(.Name, Len(.Name) - 4)
       Filename = Filename & Format(Now, "YYYY-MM-DD HH.MM")
       Filename = Filename & Right(.Name, 4)
       .SaveCopyAs Path & "BackUp " & Filename
    End If
End With
 
Last edited:
Upvote 0
if its a .xlsm or .xlsx

Code:
    Filename = Left(.Name, Len(.Name) - 5)
       Filename = Filename & " " & Format(Now, "YYYY-MM-DD HH.MM")
       Filename = Filename & Right(.Name, 5)
       .SaveCopyAs Path & "BackUp " & Filename
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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