• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Force "Save As" format

neoseo1300

New Member
Hi,
I'm trying to force users to save my spreadsheet in binary xlsb. I've looked online and followed the methodology as per:

So I've added the following code in ThisWorkbook:

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xFileName As String
If SaveAsUI <> False Then
    Cancel = True
    xFileName = Application.GetSaveAsFilename(, "Excel Binary Workbook (*.xlsb), *.xlsb", , "Save As xlsb file")
    If xFileName <> "False" Then
      Application.EnableEvents = False
      ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlExcel12
      Application.EnableEvents = True
    Else
      MsgBox "Action Cancelled"
      Cancel = True
      Exit Sub
    End If
End If
End Sub

Although the code works as intended in most cases, it returns a run-time error 1004 in certain situations such as when the window "A file named xxxx already exists in this location. Do you want to replace it?" pops up and you click on either "No" or "Cancel" or press escape.
In this case, when I then click on debug, it highlights the line:
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlExcel12

Do you know how to modify the code to prevent this type of error?

Also, in the page where I copied the code from (link above), certain comments indicate that it does not work on mac (can't confirm, I don't have a mac). Is it true? Would there be a fix for that too?

Thanks
 
neoseo1300
... some ...
Have You tried to disable alerts? ... or even try to check, if there is already same name file?
Could You use SaveCopyAs instead SaveAs?
Do user need to give 'own name'? .. Could those save in one fixed folder for use?
 
Back
Top