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:
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:
error 424: object needed
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.
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
