Search code examples
excelvbasave-asxlsb

vba routine to save workbook does not have desired result

I have a workbook which is updated every 4 weeks with new data. When updated, it has to be saved with a specific name and with specific options. I already had a script which saves workbook to a new file for me so I used that script and modified it.

Sub save_workbook_name()
Dim workbook_Name As Variant
Dim location As String

location = "N:\IRi\"
workbook_Name = Application.GetSaveAsFilename

If workbook_Name <> False Then

    ActiveWorkbook.SaveAs Filename:=Workbook.Name, WriteResPassword:="TM", FileFormat:=50

End If
End Sub

When I use this code and I press the button, a popup screen appears asking me how I want to save the file:

enter image description here

But there is not file format being set. The password for opening the file is set I noticed when opening the saved file. I know for myself that I have to add the .xslb extension when saving the file but I am not sure about any colleague whom also works with this file.

When I enter a filename and extension, I get an error:

enter image description here

error 424: object needed

enter image description here

my wished regarding to the options for saving:

  • filetype has to be set to .xlsb
  • to prevent the saved copy from being updated, I want it to be saved with password protection for opening

How can I make the routine to already add the .xlsb extension so only the file names has to be entered?

edit: with the answer from Marcucciboy2 I changed the script to:

Sub save_workbook_name()
Dim workbook_Name As Variant
Dim location As String

workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb", InitialFileName:="N:\IRi\")

If workbook_Name <> False Then

    ActiveWorkbook.SaveAs WriteResPassword:="TM", FileFormat:=50

End If
End Sub

And now it works perfectly for saving.

enter image description here

Additional question with regarding to this script and the entered name is posted in a new question: vba script to save workbook overwrites entered filename

Solution

  • I think the issue might be that you're not filtering the filename that you receive from GetSaveAsFilename, so try:

    Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb")