Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

SaveAs and FileFormat numbers in Mac Excel

Read this page about VBA SaveAs if you use Excel 2007-2016
Use VBA SaveAs in Excel 2007-2016

SaveAs and SaveCopyAs in Mac Excel

In Windows Excel 97-2016 and in Mac Excel 2011 you can open files or save files where you want in almost every folder on your system without warnings or requests for permission. But in Mac Office 2016 Microsoft have to deal with Apple’s sandbox requirements. This means that when you want to save or open files the first time you will be prompted to allow access on the first attempt to access such a folder or file.

Check out this page for more information about Mac Excel 2016 :

Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code

 

FileFormat numbers in Mac Excel

These are the main file formats in Windows Excel 2007-2016:

51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

Note: I always use the FileFormat numbers instead of the defined constants in my code so that it will compile OK when I copy the code into an Excel 97-2003 workbook. For example, Excel 97-2003 won't know what the xlOpenXMLWorkbookMacroEnabled constant is.

But on the Mac in In Excel 2011 and Excel 2016 you must add 1 to each number, sorry this is not my idea <g>.

xlsx = 52
xlsm = 53
xlsb = 51
xls = 57


This is important to know because SaveAs requires you to provide both the FileFormat parameter and the correct file extension to be sure it is saved correct.

In Windows you use this for example to save a file as xlsm

ActiveWorkbook.SaveAs "C:\Users\YourUserName\Desktop\ron.xlsm", fileformat:=52

On a Mac in Excel 2011 you must use this, note that Excel 2011 use the : as separator

ActiveWorkbook.SaveAs "Macintosh HD:Users:YourUserName:Desktop:ron.xlsm", fileformat:=53

Note : in Mac Excel 2016 your path looks like this:

/Users/YourUserName/Desktop/ron.xlsm

No harddisk name and / as separator, see the first section on this page about the problems that you can have with saving files in Mac Excel 2016.