Save .xlsx file as .xlsb

[Vbalearner85]

Hi,

I am using the below code to save the macro file as xlsx but want to change it as binary now..

ThisWorkbook.SaveAs "D:\Hub\" & Format(Date, "dd-mm-yy") & ".xlsx", xlOpenXMLWorkbook

Trying to replace the .xlsx with the .xlsb is not helping..Please advise

 

[Joe4]

You need to change the File Format too, not just the extension.
You can get this easily by using the Macro Recorder to record yourself saving a Binary Excel file, and see what code it gives you.
So something like this:

ThisWorkbook.SaveAs "D:\Hub\" & Format(Date, "dd-mm-yy") & ".xlsb", xlExcel12

Note, if this does not work, you will need to use the Macro Recorder on yours to see what value you need.
I am using Excel 365, and it looks like you are using Excel 2016. So the number after "xlExcel..." may be different for you than it is for me.

 

[Vbalearner85]

You need to change the File Format too, not just the extension.
You can get this easily by using the Macro Recorder to record yourself saving a Binary Excel file, and see what code it gives you.
So something like this:

ThisWorkbook.SaveAs "D:\Hub\" & Format(Date, "dd-mm-yy") & ".xlsb", xlExcel12

Note, if this does not work, you will need to use the Macro Recorder on yours to see what value you need.
I am using Excel 365, and it looks like you are using Excel 2016. So the number after "xlExcel..." may be different for you than it is for me.

Thanks got it...Yes, it is the same format in 2016 as well.I recorded and checked as per your advice...Regards

 

[Joe4]

Excellent!
Glad I was able to help.