Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beforesave - Save copy on my desktop
Hi.
I have this code that i was running "beforeclose", however I adjusted it to try to make it a "beforesave" code however, it doesnt work. No error messages, but when i check my desktop, its not there. Any advice? Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Application.DisplayAlerts = False Set WshShell = CreateObject("WScript.Shell") ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\" & ThisWorkbook.Name Application.DisplayAlerts = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beforesave - Save copy on my desktop
Are you sure you want SaveAs rather than SaveCopyAs? Also, you should
disable events before doing the save -- otherwise you'll loop. Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, _ ByRef Cancel As Boolean) Dim FName As String On Error GoTo EXIT_SUB: Application.EnableEvents = False Application.DisplayAlerts = False Set WshShell = CreateObject("WScript.Shell") FName = WshShell.SpecialFolders("Desktop") & _ "\" & ThisWorkbook.Name ThisWorkbook.SaveCopyAs Filename:=FName EXIT_SUB: Application.DisplayAlerts = True Application.EnableEvents = True End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "J.W. Aldridge" wrote in message ups.com... Hi. I have this code that i was running "beforeclose", however I adjusted it to try to make it a "beforesave" code however, it doesnt work. No error messages, but when i check my desktop, its not there. Any advice? Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Application.DisplayAlerts = False Set WshShell = CreateObject("WScript.Shell") ThisWorkbook.SaveAs Filename:=WshShell.SpecialFolders("Desktop") & "\" & ThisWorkbook.Name Application.DisplayAlerts = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beforesave - Save copy on my desktop
WOW....The Legend Himself???
(Man, I am honored!) Yes, I believe Save a copy as is what I am trying to do. Would that change the code recommended? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beforesave - Save copy on my desktop
It shouldn't make a difference in the code whether you use SaveAs or
SaveCopyAs. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "J.W. Aldridge" wrote in message ups.com... WOW....The Legend Himself??? (Man, I am honored!) Yes, I believe Save a copy as is what I am trying to do. Would that change the code recommended? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Desktop Save Question | Excel Discussion (Misc queries) | |||
Save .xls as .txt In DeskTop | Excel Programming | |||
Save method and BeforeSave event | Excel Programming | |||
Detecting Save vs SaveAs in BeforeSave | Excel Programming | |||
BeforeSave sub won't save another workbook when triggered by another event sub | Excel Programming |