Date Stamp Save

[oberon.black]

I have created the following code:


Code:
--------------------

Sub Button25_Click()
'save workbook and email
ActiveWorkbook.SaveAs Filename:="C:\TGP.xls"
ActiveWorkbook.SendMail Recipients:="(e-mail address removed)"
End Sub

--------------------


I would like to know how I can add two things to this code.

1. I would like the code to create a folder in my C: drive named
BackupTGP.
2. I would like to have the filename be the current date and time.

Then reason I want to do this is so that I can continuosly save the
time/date stamped file to that folder as a backup to the save in the
'My Doc' folder.

 

[David Lloyd]

One alternative for the folder creation is to use the FileSystemObject. You
will need a reference to the Microsoft Scripting Runtime. For example:

Function CreateDirectory()
Dim fso As New Scripting.FileSystemObject

fso.CreateFolder "C:\BackupTGP"

If fso.FolderExists("C:\BackupTGP") Then
MsgBox "Folder successfully created", vbInformation, "Create Folder"
Else
MsgBox "Folder not created!", vbCritical, "Create Folder"
End If

Set fso = Nothing

End Function

Regarding saving the workbook with a date/time file name, you can use
something like the following.

Dim sFileName As String

sFileName = "C:\BackupTGP\" & Format(Date, "yyyymmdd") & "_" &
Format(Time(), "hhmmss") & ".xls"

ActiveWorkbook.SaveAs sFileName

You can choose the appropriate date and time format according to your own
preferences.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"oberon.black" <[email protected]>
wrote in message

I have created the following code:


Code:
--------------------

Sub Button25_Click()
'save workbook and email
ActiveWorkbook.SaveAs Filename:="C:\TGP.xls"
ActiveWorkbook.SendMail Recipients:="(e-mail address removed)"
End Sub

--------------------


I would like to know how I can add two things to this code.

1. I would like the code to create a folder in my C: drive named
BackupTGP.
2. I would like to have the filename be the current date and time.

Then reason I want to do this is so that I can continuosly save the
time/date stamped file to that folder as a backup to the save in the
'My Doc' folder.

 

[Dave Peterson]

I would use:

on error resume next
mkdir "C:\backupTGP"
on error goto 0

If it doesn't exist, it'll get created. If it already exists, it'll cause an
error -- but that error will be ignored because of the "on error resume next"
line.

And just another alternative to David Lloyd's suggestion:

Dim sFileName As String
sFileName = "C:\BackupTGP\" & Format(now, "yyyymmdd_hhmmss") & ".xls"