Force user to save as new file and NOT overwrite MASTER file

[PJGallagher]

Hi,

I have a MASTER file that imports data from a .csv file via a macro. I want to force the user to then have to save the file as a new file (NOT overwrite the MASTER file), ensuring the MASTER file is intact and blank for the next run (weekly action to import and report data to seniors). I am using the below code which works UNLESS the user chooses 'Cancel' when the prompt to save the file is presented. Pressing Cancel actually saves a file in the default directory with the name "false.xls"?! Any help appreciated...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strFileName As String
Const strRestrictedName As String = "MASTER_FILE.xls"
Application.EnableEvents = False
Cancel = True
strFileName = Application.GetSaveAsFilename(fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
strFileName = Mid$(strFileName, InStrRev(strFileName, "\") + 1)
If UCase$(strFileName) = UCase$(strRestrictedName) Then
MsgBox "Invalid File Name!" & vbCrLf & vbCrLf & "Saving this file as the MASTER file is not allowed.", vbCritical, "Stop"
Else
ActiveWorkbook.SaveAs strFileName
End If
Application.EnableEvents = True
End Sub


I know I can make the MASTER file read only, but then the user could change this attribute and overwrite.

If there are other more successful ways to enforce this, I'd appreciate your insight into how to do this.

Thank you so much in advance

Paul

[Kenneth Hobs]

If a macro is ran, why not make it start by creating a new workbook and import data to that file?

[PJGallagher]

If a macro is ran, why not make it start by creating a new workbook and import data to that file?

Good suggestion - I'll have a look at that, thanks.

Is there anything obviously wrong with above code (or how to improve it) to stop the "false.xls" file being created when/if user clicks cancel to save operation?

[SamT]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Const strRestrictedName As String = "MASTER_FILE.xls"
Dim strFileName As String

Cancel = True

TryAgain:
strFileName = Application.GetSaveAsFilename(fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
If LCase(strFileName) = "false" Then Exit Sub
strFileName = Mid$(strFileName, InStrRev(strFileName, "\") + 1)

If UCase$(strFileName) = UCase$(strRestrictedName) Then
MsgBox "Invalid File Name!" & vbCrLf & vbCrLf & "Saving this file as the MASTER file is not allowed.", vbCritical, "Stop"
GoTo TryAgain
Else
ActiveWorkbook.SaveAs strFileName
End If

End Sub

[PJGallagher]

WOW! Perfect - works a treat now - thank you so much :-)