Restrict File Save in .xls format

[chasoe]

Dear Experts,

I consistently receive many Excel report files with macros in them, but some files received have been mistakenly removed the macros due to saving them under .xlsx format.

For this reason, I've put the below macro in VBA Project Explorer - Workbook TAB to restrict saving the file in .xls. However there is a flaw in that this macro in that, when we save the file it will not prompt the default folder from which the file is opened, but prompt other folders especially when other workbooks are opened from "other" folders.

I've searched from internet and created a macro as below :

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     
    Dim strFileName As String
    Const strRestrictedName As String = "False"
    
    Application.EnableEvents = False
 
    Cancel = True
 
    strFileName = Application.GetSaveAsFilename(fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
    
    If UCase$(strFileName) = UCase$(strRestrictedName) Then
    
      MsgBox "Invalid File Name", vbCritical, "Stop"
      Application.EnableEvents = True
    
    ElseIf strFileName = "" Then
    
       MsgBox "Invalid File Name", vbCritical, "Stop"
       Application.EnableEvents = True
       
    Else
        
        ActiveWorkbook.SaveAs strFileName
        Application.EnableEvents = True
    
    End If
 
    Application.EnableEvents = True
 
End Sub

Would like someone who can help fine-tune the scripts.

Many thanks !

Edward

[bakerman2]

You can set InitialFileName when using GetSaveAsFilename.

strFileName = Application.GetSaveAsFilename(InitialFileName:="D:\Test", fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")

As for SaveAs you can set default FileFormat

ActiveWorkbook.SaveAs strFileName, 50

Does this help you any further ?

[chasoe]

But the file can be in any folder, and will "InitialFileName:="D:\Test" be inappropriate ?
The file will be saved under different folder each month, so fixing the folder to D:\Test is not possible.

Any further advice ?

[bakerman2]

Do you open the file via code or is it opened manually.

If it's opened manually how can Excel ever know from which directory the file was opened ?

[chasoe]

The file is opened manually.

In fact, without the codes, a normal file is saved by defaut to existing folder from which the file is originally opened (without any screen prompts), and if you close the file with file changes the system wil prompt you whether you wish to save the file with changes you made.
And only when you use SAVEAS, there will be a pop-up screen asking user which folder to save.

I just wish to retain the above feature, but the codes I used will always prompt me where to save, and worst of all, not prompt me the default folder from which the file is opened, and I often saved the file to the wrong folder different from the folder it originally resides.

See if you understand what I expect.

Thx again.

Regards,

[bakerman2]

How about this.

strFileName = Application.GetSaveAsFilename(InitialFileName:=Thisworkbook.path, fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")

[chasoe]

Thx for your further advice.

I've just test other alternative basing on your suggestion to add "Thisworkbook.path", by adding Workbook Name after it.

strFileName = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path & "\" & ActiveWorkbook.Name, fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")

This is close enough to what I expected, but I've another doubt :

For some unknown reason, the codes sometimes seem not able to take control (as if they are absent), file save or saveas will just return to normal file save feature (ie will not restrict to .xls, etc). Seems this will happen especially when I've also opened other workbooks in same Excel and perform some other tasks.

Any idea ?

[bakerman2]

Setting Fileformat to 50 on SaveAs will always save your file as '.xls'.

[chasoe]

OK. In fact, I've tried adding Fileformat 50 to the codes, but since I cannot figure out correctly the upper portion of the codes, so I didn't notice the effect of adding "50" to the codes.

I seem to understand now on my doubt for the codes sometimes not able to take control of SAVE/SAVEAS function :
Perhaps when I'm debugging the codes, and when the codes run up to just beyond the 3rd line as below (which will disable the macro) and stops with code error, then subsequent SAVE / SAVEAS will not call up the codes again.

Application.EnableEvents = False

In any case, my problem is solved now.

Many thanks indeed.