Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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" wrote: 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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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) "appeng" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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 "Bob Phillips" wrote in message ... 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) "appeng" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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 = "~~~ " "SixSigmaGuy" wrote in message ... 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 "Bob Phillips" wrote in message ... 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) "appeng" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
Still comes up with comply error "expected: end of statement" and highlights
Filename Art "Bob Phillips" wrote: 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) "appeng" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
Thank you - Will give it a try.
Art "SixSigmaGuy" wrote: 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 = "~~~ " "SixSigmaGuy" wrote in message ... 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 "Bob Phillips" wrote in message ... 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) "appeng" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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) "appeng" wrote in message ... Still comes up with comply error "expected: end of statement" and highlights Filename Art "Bob Phillips" wrote: 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) "appeng" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Backup with date-time
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 appeng wrote: Still comes up with comply error "expected: end of statement" and highlights Filename Art "Bob Phillips" wrote: 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) "appeng" wrote in message ... 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" wrote: 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" wrote in message ... 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminate creation of backup document every time I open workbook? | Excel Discussion (Misc queries) | |||
Converting text format of time/date into Excel time/date for subtr | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions |