File SaveAS

[Guest]

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.

 

[Jim Cone]

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"
<[email protected]>
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.

 

[Guest]

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]

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

 

[Guest]

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]

You are welcome, the feedback is appreciated.
Jim Cone


"Mickmoo" <[email protected]>
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