Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
Greetings:
I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" End With Application.Quit ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote:
Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" End With Application.Quit ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Try this. You do not need to restore alerts, etc if you QUIT Sub archive_and_exitSAS() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error GoTo nofind With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & _ Format(Now, "mmm dd, yyyy-hhmm AM/PM") & ".xlsb" End With Application.Quit nofind: 'MsgBox "nofind" Application.DisplayAlerts = True Application.ScreenUpdating = True Exit Sub End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Jun 24, 8:20*am, Don Guillett wrote:
On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False * * On Error Resume Next * * With ThisWorkbook * * * * .SaveAs Filename:="\\test\test1" & ".xlsb" * * * * .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" * * End With * * * *Application.Quit * * * *ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Try this. You do not need to restore alerts, etc if you QUIT Sub archive_and_exitSAS() Application.ScreenUpdating = False Application.DisplayAlerts = False *On Error GoTo nofind *With ThisWorkbook * .SaveAs Filename:="\\test\test1" & ".xlsb" * .SaveAs Filename:="\\test\test2" & _ * *Format(Now, "mmm dd, yyyy-hhmm AM/PM") & ".xlsb" *End With Application.Quit nofind: 'MsgBox "nofind" Application.DisplayAlerts = True Application.ScreenUpdating = True Exit Sub End Sub Thanks, Don. I appreciate your response and suggestion. I tried the modification you suggested, but when it executes, Excel still goes into a mode where it appears to be looking for the path. Consequently, when it doesn't find the path, it freezes (e.g., the hourglass is diplayed, and eventually "not responding" appears in the window title frame). Do you think that giving it 10 secs or so to find the path and then going to nofind would work? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Jun 24, 8:55*am, icystorm wrote:
On Jun 24, 8:20*am, Don Guillett wrote: On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False * * On Error Resume Next * * With ThisWorkbook * * * * .SaveAs Filename:="\\test\test1" & ".xlsb" * * * * .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" * * End With * * * *Application.Quit * * * *ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Try this. You do not need to restore alerts, etc if you QUIT Sub archive_and_exitSAS() Application.ScreenUpdating = False Application.DisplayAlerts = False *On Error GoTo nofind *With ThisWorkbook * .SaveAs Filename:="\\test\test1" & ".xlsb" * .SaveAs Filename:="\\test\test2" & _ * *Format(Now, "mmm dd, yyyy-hhmm AM/PM") & ".xlsb" *End With Application.Quit nofind: 'MsgBox "nofind" Application.DisplayAlerts = True Application.ScreenUpdating = True Exit Sub End Sub Thanks, Don. I appreciate your response and suggestion. I tried the modification you suggested, but when it executes, Excel still goes into a mode where it appears to be looking for the path. Consequently, when it doesn't find the path, it freezes (e.g., the hourglass is diplayed, and eventually "not responding" appears in the window title frame). Do you think that giving it 10 secs or so to find the path and then going to nofind would work? Update: I attempted to conditionally qualify if the UNC path exists by using... If Dir("//test/", vbDirectory) < "" Then ....but the script (macro) fails on that line. I think the syntax may be wrong. Instead of Dir and vbDirectory, is the syntax different, such as Path and vbPath? I looked and could not find any references other than Dir and vbDirectory. Those may only refer to the mapped drive. I need to verify if the specific UNC path exists before proceeding, otherwise, my macro hangs while it is presumably looking for the UNC path and not finding it. Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote:
Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" End With Application.Quit ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub I tested this on my computer with xl2003. On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" End With Application.Quit ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" End With Application.Quit ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub original worked in xl2003 but not xl2007 or xl2010. However, this works in all. Sub archive_and_exitSAS1() dim mf as string mf = "\desiredfoldernamehere\" If Dir(mf) < "" Then MsgBox "Yes, path exists" With ThisWorkbook .SaveAs Filename:=mf & "test1" & ".xlsb" .SaveAs Filename:=mf & "test2" & _ Format(Now, "mmm dd, yyyy-hhmm AM/PM") & ".xlsb" End With Application.Quit Else MsgBox "No, path does not exist." End If End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Jun 24, 5:00*pm, Don Guillett wrote:
On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False * * On Error Resume Next * * With ThisWorkbook * * * * .SaveAs Filename:="\\test\test1" & ".xlsb" * * * * .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" * * End With * * * *Application.Quit * * * *ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub I tested this on my computer with xl2003. On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False * * On Error Resume Next * * With ThisWorkbook * * * * .SaveAs Filename:="\\test\test1" & ".xlsb" * * * * .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" * * End With * * * *Application.Quit * * * *ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False * * On Error Resume Next * * With ThisWorkbook * * * * .SaveAs Filename:="\\test\test1" & ".xlsb" * * * * .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" * * End With * * * *Application.Quit * * * *ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub original worked in xl2003 but not xl2007 or xl2010. However, this works in all. Sub archive_and_exitSAS1() dim mf as string mf = "\desiredfoldernamehere\" If Dir(mf) < "" Then MsgBox "Yes, path exists" With ThisWorkbook * .SaveAs Filename:=mf & "test1" & ".xlsb" * .SaveAs Filename:=mf & "test2" & _ * *Format(Now, "mmm dd, yyyy-hhmm AM/PM") & ".xlsb" *End With Application.Quit Else MsgBox "No, path does not exist." End If End Sub Thanks, Don! One minor issue. It works with this... mf = "\desiredfoldernamehere\" but not with this... mf = "\\desiredfoldernamehere\" Isn't a double-forwardslash required for a UNC path? That's what I require (UNC, not mapped), but it fails when I use the correct syntax for a UNC path. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote:
Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" End With Application.Quit ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Look here for possible answers http://tinyurl.com/79vrwqq |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Jun 25, 7:26*am, Don Guillett wrote:
On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote: Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False * * On Error Resume Next * * With ThisWorkbook * * * * .SaveAs Filename:="\\test\test1" & ".xlsb" * * * * .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" * * End With * * * *Application.Quit * * * *ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Look here for possible answershttp://tinyurl.com/79vrwqq Thanks again for your help, Don. I appreciate your assistance. I had looked extensively though many of those options you suggested before posting here, but without success. I began looking for some ancillary and similar problems today and found some hints that helped. Using several similar solutions, I was able to craft the following, which works perfectly with a UNC path. I'm not sure if it is as efficient as it could or should be, but it works perfectly for both cases of when the UNC path is available and is not available. If anyone has any recommendations to tidy this up, please share... Otherwise, I'm happy with the way it is. Sub archive_and_exit() On Error Resume Next stringvariable = "" stringvariable = Dir("\\your\UNC\path\here\", vbDirectory) If Err.Number < 0 Then MsgBox "Cannot archive. Server UNC Path \\your\UNC\path\here\ is not available." Err.Clear End If If stringvariable < "" Then With ThisWorkbook .SaveAs Filename:="\\your\UNC\path\here\filename.xxx" End With Application.Quit End If End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
save and exit macro
On Sunday, June 24, 2012 7:39:43 AM UTC-5, icystorm wrote:
Greetings: I wrote a macro (below) to save a workbook to two files and exit. If the paths are not available, the workbook should not be saved and Excel should simply quit. I think I may be misuing On Error Resume Next below, because the fact that the script is not finding the path is not causing an error. Any advice? Again, if the path is not found, I want Excel to simply exit (after restoring screenupdating and displayalerts). All changes are intended to be discarded if the script is unable to locate and save to the identified path. Thanks! --- Sub archive_and_exit() Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next With ThisWorkbook .SaveAs Filename:="\\test\test1" & ".xlsb" .SaveAs Filename:="\\test\test2" & Format(Now, "mmm dd, yyyy - hhmm AM/PM") & ".xlsb" End With Application.Quit ActiveWorkbook.Close False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Glad it works. The only thing I would recommend is write your code in the manner of the one I provided. However, no big deal either way. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save/exit macro | Excel Programming | |||
Excel Save as & Exit macro | Excel Programming | |||
running macro at save/close/exit | Excel Programming | |||
Write macro don't ask to save when exit excel | Excel Programming | |||
Macro: Exit active workbook without save? | Excel Worksheet Functions |