Backup with date-time

[appeng]

I am using MS Excel 97 and trying to save a backup copy of files, with date
and time saved, to a specific directory while maintaining the original file
name for the active workbook. For example

Original workbook 1 name = c:\data\schedule.xls
Original workbook 2 name = c:\data\data.xls

When saved, save a copy in c:\temp
With file name schedule NOV0504 623AM.xls
Or data NOV404 703PM.xls

The original file would remain
c:\data\schedule.xls and c:\data\data.xls

I have tried the following but cannot get it to work

Sub Save_Workbook()
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:= _
“c:\temp\†& _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xls") - 1) _
& Format(Date, "mmddyy") & Format(Date, "hhmmAMPM") & ".xls"
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

I am sure I am missing something simple. Any suggestions?

 

[Bob Phillips]

Does this work

Sub Save_Workbook()
Application.DisplayAlerts = False
With ActiveWorkbook.
SaveCopyAs FileName:= "c:\temp\" & _
Left(.Name, InStr(1, LCase(ThisWorkbook.Name), ".xls") - 1) & _
Format(Now, "mmddyyhhmmAMPM") & ".xls"
End With
Application.Display = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[appeng]

Bob,

Thank you! I am having a complie error. It does not like

FileName:=

But I am still working on it. I believe that is is domething simple, but not
having a lot of VBA knowledge . . .


Art

 

[Bob Phillips]

How about this

Application.DisplayAlerts = False
With ActiveWorkbook
.SaveCopyAs Filename:="c:\temp\" & _
Left(.Name, InStr(1, LCase(.Name), ".xls") - 1) & _
Format(Now, "mmddyyhhmmAMPM") & ".xls"
End With
Application.Display = True

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[SixSigmaGuy]

Couldn't help noticing this thread. I don't think I'm addressing your exact
problem, but I have to do this sort of thing quite often and a long time ago
wrote some routines for handling it. I use a format I call "sortable dates"
so that it produces a file name that I can sort on by date (year first, then
month, then day, hour, minute, second):

This function creates the filename with the date included in the name:

Public Function BuildDateSortableFileName(stFilenameIn As String, dat As
Date, Optional stFormat) As String
'
' This routine converts a date into a sortable format:
' ("y" & Year & "m" & Month & "d" & Day & "h" & Hour & "m" & Minute & "s" &
Second)
'
' and prepends it to the filename passed in. This allows you to sort the
files by date
' even if the actual file dates change (e.g., when they get recorded to CD.
'
' The characters "~~~ " are added immediately before the original file name
so the dates
' can be easily parsed out later.
'
' stFilename: Original filename. If stFilename contains path information,
this is removed
' prior to prepending the date and the replaced before
returning.
' dat: The date you want prepended.
'
' stFormat: used if you want to override the default date format string
"\yyyyy\mmm\ddd \hHh\mNn\sSs"
'
Dim stFn As String
Dim stPath As String
Dim stSortableDate As String
Dim iPos As Integer
Dim stFilenameOut As String

If IsMissing(stFormat) Then
stFormat = SORTABLE_DATE_FORMAT_STRING
End If

stSortableDate = Format(dat, stFormat)

iPos = InStrRev(stFilenameIn, "\")
If iPos > 0 Then
stPath = Trim(Left(stFilenameIn, iPos - 1))
stFn = Trim(Mid(stFilenameIn, iPos + 1))
Else
stFn = Trim(stFilenameIn)
stPath = ""
End If

stFilenameOut = stPath & "\" & stSortableDate &
SORTABLE_DATE_DELINEATION_STRING & stFn

BuildDateSortableFileName = stFilenameOut
End Function

Sometimes I need to reverse the operation and use this routine. But it only
works if I use my default format.

Public Function ConvertSortableDateToDate(stSortableDate As String) As Date
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim iHour As Integer
Dim iMinute As Integer
Dim iSecond As Integer
Dim iPosCurr As Integer
Dim iPosNext As Integer
Dim iPosStart As Integer
Dim dat As Date

iPosStart = 1

iPosCurr = InStr(iPosStart, stSortableDate, "y")
iYear = CInt(Mid(stSortableDate, iPosCurr + 1, 4))

iPosCurr = InStr(iPosStart, stSortableDate, "m")
iMonth = CInt(Mid(stSortableDate, iPosCurr + 1, 2))

iPosCurr = InStr(iPosStart, stSortableDate, "d")
iDay = CInt(Mid(stSortableDate, iPosCurr + 1, 2))

iPosCurr = InStr(iPosStart, stSortableDate, "h")
iHour = CInt(Mid(stSortableDate, iPosCurr + 1, 2))

iPosStart = iPosCurr

iPosCurr = InStr(iPosStart, stSortableDate, "m")
iMinute = CInt(Mid(stSortableDate, iPosCurr + 1, 2))

iPosCurr = InStr(iPosStart, stSortableDate, "s")
iSecond = CInt(Mid(stSortableDate, iPosCurr + 1, 2))

dat = DateSerial(iYear, iMonth, iDay) & " " & TimeSerial(iHour, iMinute,
iSecond)

ConvertSortableDateToDate = dat
End Function

 

[SixSigmaGuy]

Ooops... Forgot to include the two constants I have used in the code.

Const SORTABLE_DATE_FORMAT_STRING = "\yyyyy\mmm\ddd \hHh\mNn\sSs"
Const SORTABLE_DATE_DELINEATION_STRING = "~~~ "

 

[appeng]

Still comes up with comply error "expected: end of statement" and highlights
Filename

Art

 

[appeng]

Thank you - Will give it a try.

Art

Ooops... Forgot to include the two constants I have used in the code.

Const SORTABLE_DATE_FORMAT_STRING = "\yyyyy\mmm\ddd \hHh\mNn\sSs"
Const SORTABLE_DATE_DELINEATION_STRING = "~~~ "

 

[Bob Phillips]

It works fine for me.

Are you sure you are doing this with a previously saved workbook? Otherwise
your Instr looking for xls won't work.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

You may want to post the code you used if Bob's answer didn't help. His code
worked fine for me after I saved the test workbook once.

But I did have to change the last line to:

Application.DisplayAlerts = True