save and exit macro

  • Thread starter Thread starter icystorm
  • Start date Start date
I

icystorm

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
 
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
Click to expand...

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
 
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
Click to expand...

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?
 
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?
Click to expand...

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!
 
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
Click to expand...

I tested this on my computer with xl2003.

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
Click to expand...



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
Click to expand...

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
 
I tested this on my computer with xl2003.






















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
Click to expand...

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.
 
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
Click to expand...

Look here for possible answers
http://tinyurl.com/79vrwqq
 
Look here for possible answershttp://tinyurl.com/79vrwqq
Click to expand...

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
 
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
Click to expand...

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