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.

Important:
In Excel 2011 and Excel 2016 version 15 you must add 1 to each number, sorry this is not my idea . So on a Mac you must test the version number and use the correct FileFormat number if you an older Excel version.

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
31-July-2021
Web design by Will Woodgate