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.
|