File SaveAS
 

Hello,

I'm rather new to Excel coding so forgive me if this question sounds naive.

I'm trying to find a property that that I can use in the Workbook_BeforeSave
event in order to identify whether the user is trying to overwrite a specific
file on disk (by using the File SaveAs command), so that I can inform them of
this and cancel the Save event.

Thanks in advance for any assistance.

File SaveAS
 

Excel automatically displays a message if a file is being overwritten
unless your code suppresses the display of alert messages.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Mickmoo"

wrote in message
Hello,
I'm rather new to Excel coding so forgive me if this question sounds naive.
I'm trying to find a property that that I can use in the Workbook_BeforeSave
event in order to identify whether the user is trying to overwrite a specific
file on disk (by using the File SaveAs command), so that I can inform them of
this and cancel the Save event.
Thanks in advance for any assistance.

File SaveAS
 

Thanks Jim,

I follow the point about Excel displaying a message enabling the user to
cancel the operation (assuming displayalerts isn't suppressed) , but they
still have the ability to click Yes and overwrite the file.
What I need to be able to do is trap the new filename before the save
operation operation occurs, compare it with the name of the file I do not
wish overwritten, and then cancel the save operation if the two names match.

Can this be done?

Many thanks


"Jim Cone" wrote:

Excel automatically displays a message if a file is being overwritten
unless your code suppresses the display of alert messages.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Mickmoo"

wrote in message
Hello,
I'm rather new to Excel coding so forgive me if this question sounds naive.
I'm trying to find a property that that I can use in the Workbook_BeforeSave
event in order to identify whether the user is trying to overwrite a specific
file on disk (by using the File SaveAs command), so that I can inform them of
this and cancel the Save event.
Thanks in advance for any assistance.

File SaveAS
 

Code goes in the ThisWorkbook module...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
On Error GoTo PrintingOver
Application.EnableEvents = False
Dim varResponse As Variant

varResponse = Application.GetSaveAsFilename( _
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

If varResponse = False Then
'do nothing except exit
ElseIf varResponse = Me.FullName Then
MsgBox "Please save under a different name. "
Else
ThisWorkbook.SaveAs varResponse
End If

PrintingOver:
Application.EnableEvents = True
Cancel = True
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html.



"Mickmoo"
wrote in message
Thanks Jim,
I follow the point about Excel displaying a message enabling the user to
cancel the operation (assuming displayalerts isn't suppressed) , but they
still have the ability to click Yes and overwrite the file.
What I need to be able to do is trap the new filename before the save
operation operation occurs, compare it with the name of the file I do not
wish overwritten, and then cancel the save operation if the two names match.
Can this be done?
Many thanks


"Jim Cone" wrote:
Excel automatically displays a message if a file is being overwritten
unless your code suppresses the display of alert messages.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Mickmoo"

wrote in message
Hello,
I'm rather new to Excel coding so forgive me if this question sounds naive.
I'm trying to find a property that that I can use in the Workbook_BeforeSave
event in order to identify whether the user is trying to overwrite a specific
file on disk (by using the File SaveAs command), so that I can inform them of
this and cancel the Save event.
Thanks in advance for any assistance.

File SaveAS
 

Jim,

That did the trick.

I never thought of calling the GetFileNameAs method and capturing the user's
response. We live and learn!

You're a star and a credit to the community.

Many thanks

"Jim Cone" wrote:

Code goes in the ThisWorkbook module...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
On Error GoTo PrintingOver
Application.EnableEvents = False
Dim varResponse As Variant

varResponse = Application.GetSaveAsFilename( _
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

If varResponse = False Then
'do nothing except exit
ElseIf varResponse = Me.FullName Then
MsgBox "Please save under a different name. "
Else
ThisWorkbook.SaveAs varResponse
End If

PrintingOver:
Application.EnableEvents = True
Cancel = True
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html.



"Mickmoo"
wrote in message
Thanks Jim,
I follow the point about Excel displaying a message enabling the user to
cancel the operation (assuming displayalerts isn't suppressed) , but they
still have the ability to click Yes and overwrite the file.
What I need to be able to do is trap the new filename before the save
operation operation occurs, compare it with the name of the file I do not
wish overwritten, and then cancel the save operation if the two names match.
Can this be done?
Many thanks


"Jim Cone" wrote:
Excel automatically displays a message if a file is being overwritten
unless your code suppresses the display of alert messages.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Mickmoo"

wrote in message
Hello,
I'm rather new to Excel coding so forgive me if this question sounds naive.
I'm trying to find a property that that I can use in the Workbook_BeforeSave
event in order to identify whether the user is trying to overwrite a specific
file on disk (by using the File SaveAs command), so that I can inform them of
this and cancel the Save event.
Thanks in advance for any assistance.

File SaveAS
 

You are welcome, the feedback is appreciated.
Jim Cone


"Mickmoo"
wrote in message
Jim,
That did the trick.
I never thought of calling the GetFileNameAs method and capturing the user's
response. We live and learn!
You're a star and a credit to the community.
Many thanks