Date Stamp Save

[oberon.black]

I have created the following code:

Please Login or Register  to view this content.

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
news:[email protected]...

I have created the following code:


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

Sub Button25_Click()
'save workbook and email
ActiveWorkbook.SaveAs Filename:="C:\TGP.xls"
ActiveWorkbook.SendMail Recipients:="[email protected]"
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.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=466512

[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"


"oberon.black" wrote:
>
> I have created the following code:
>
> Code:
> --------------------
>
> Sub Button25_Click()
> 'save workbook and email
> ActiveWorkbook.SaveAs Filename:="C:\TGP.xls"
> ActiveWorkbook.SendMail Recipients:="[email protected]"
> 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.
>
> --
> oberon.black
> ------------------------------------------------------------------------
> oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
> View this thread: http://www.excelforum.com/showthread...hreadid=466512

--

Dave Peterson