SaveAs and FileFormat numbers on Mac
SaveAs and SaveCopyAs in Mac Excel
Note : When you use VBA in Mac Excel 2016 or higher that is working with files and folders you will notice that it is possible that it will ask you permission to access the file or folder (Grant File Access Prompt), this is because of Apple’s sandbox requirements.This means that when you want to save/open files or check if it exists with VBA code the first time you will be prompted to allow access on the first attempt to access such a folder or file. If you want to avoid problems like this read this page : Problems with Apple’s sandbox requirements
FileFormat numbers in Mac Excel
These are the main file formats in Windows Excel 2007 and higher and Mac Excel 2016(up to date) and higher:
51 = xlOpenXMLWorkbook (without macro's in 2007 and higher, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007 and higher, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 and higher with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007 and higher, 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.
In Excel 2011 and Excel 2016 version 15 you must add 1 to each number, sorry this is not my idea
52 = (without macro's in Mac Excel 2011 and version 15 of Mac Excel 2016, xlsx)
53 = (with or without macro's in Mac Excel 2011 and version 15 of Mac Excel 2016, xlsm)
51 = (with or without macro's in Mac Excel 2011 and version 15 of Mac Excel 2016, xlsb)
57 = (97-2003 format in Mac Excel 2011 and version 15 of Mac Excel 2016, xls)
See this pages for information how to test the OSX and Mac Excel version number on your Mac.
Mac Excel Version and Mac Office Language settings
Test if it is a Mac or a Windows Machine
The correct FileFormat number 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. If they not correct you can't open the file you saved.
To save as xlsm file it looks like this
ActiveWorkbook.SaveAs "/Users/rondebruin/Desktop/ron.xlsm", fileformat:=52