PDA

View Full Version : [SOLVED] Save wookbook as now() function



Mjack59_59
03-20-2005, 07:30 AM
Hello Again everyone,

Hopefully some one can help me out again. The problem is I had this code (below):banghead: working in my office, but not the one @ home. Could it be my copy paste may have lost something. The offensive code appears to be in the line formated in red. Any and all help is greatly appriciated



Application.DisplayAlerts = False
Application.EnableEvents = False
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h:mm a/p") & ".xls"
.save
Application.DisplayAlerts = True
Application.EnableEvents = True

PS Folk754, Thanks for the tip on rolling up sheets works great:thumb

Paleo
03-20-2005, 08:15 AM
Hi Mjack59_59,

simply adapt the code, changing it from



Application.DisplayAlerts = False
Application.EnableEvents = False
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h:mm a/p") & ".xls"
.save
Application.DisplayAlerts = True
Application.EnableEvents = True


to


With Application
.DisplayAlerts = False
.EnableEvents = False
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h_mm a/p") & ".xls"
.Save
.DisplayAlerts = True
.EnableEvents = True
End With

mdmackillop
03-20-2005, 08:23 AM
Hi Mjack,
The colon in h:mm is an illegal character for a filename, try h_mm.
You also need an ActiveWork book, if it's not set earlier in your code


With ActiveWorkbook
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h_mm a/p") & ".xls"
.Save
End With

MD

Mjack59_59
03-20-2005, 10:37 AM
Hello gentlemen,

Thanks for the quick replies, but still unable to resolve problem. What I'm doing is adding a client to a list then clicking a button to sort the list save to backups dir in same path and then change to different sheet. The rows after dim Str and before .save seem to be the problem.

Thanks for all the help
Mjack59


Sub sortdatabase()
' sortdatabase and move to link sheet macro
' Macro recorded 4/5/2004 by Mark Jackson
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Range("A3").Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
With ThisWorkbook
Dim Str_TargetFile As String
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h_mm a/p") & ".xls"
.save
End With
Sheets("Link SpreadSheet").Select
Range("E1").Select
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

mdmackillop
03-20-2005, 10:47 AM
I think then that your problem is here

LCase(ThisWorkbook.Name), ".xlt")
You're looking for the template extension in the workbook name. Try ".xls"
Secondly I would close and reopen the application With. While it may not cause any problems, nested with statements just seem to complicate things


With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
end with

Mjack59_59
03-20-2005, 11:05 AM
Thanks to all

mdmackillop, that was the problem. thanks again
the help on this site is the best around

Mjack59

mdmackillop
03-20-2005, 11:10 AM
Glad to help Mark.