PJGallagher
08-29-2013, 03:37 AM
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
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