And let's start by taking a look at what is, perhaps, the simplest case of opening an Excel workbook using VBA: How To Open A Workbook Using VBA: The Basic Case. Within Visual Basic for Applications, the method that opens an Excel workbook is the Workbooks.Open method. The Workbooks.Open method has 15 optional arguments. Each of these 15. This tutorial is for beginners only. For Loop, Nested For Loop. Example has been given on Mac, but applies on Windows PC as well.
Select files on a Mac (GetOpenFilename)
In Windows we can use for example GetOpenFilename to select files and do what we want with the path results, you can use filefilter to only display the files you want and use MultiSelect to select more then one file. Also it is possible with ChDrive and ChDir to set the folder that is selected when GetOpenFilename opens, see a example on the bottom of this page for Excel for Windows.
But on a Mac the filefilter is not working and it is not possible to select more then one file. Also ChDir is not working like in Windows to set the folder that will open with GetOpenFilename. But we can use a combination of VBA and Applescript, see example below that only let you select xlsx files and you can set the start folder.
Important : The file location can be very important in Mac Excel 2016, read : Problems with Appleās sandbox requirements in Mac Office 2016 and higher with VBA code
Example for Mac Excel 2011 and 2016 and higher
Note : Do not forget to copy the bIsBookOpen function below the macro.
You can run the macro below without changing it, it opens the Desktop in the file select dialog and you can only select one xlsx file now, see the code how to change this.
Note : If you got problems with the code please report it to me so i can fix it.
Other file formats are :
In the macro you see this code line that say which file format you can select (xlsx).
FileFormat = '{'org.openxmlformats.spreadsheetml.sheet'}'
If you want more then one format you can use this to be able to also select xls files.
FileFormat = '{'org.openxmlformats.spreadsheetml.sheet','com.microsoft.Excel.xls'}'
This is a list of a few formats that you can use :
xls : com.microsoft.Excel.xls
xlsx : org.openxmlformats.spreadsheetml.sheet
xlsm : org.openxmlformats.spreadsheetml.sheet.macroenabled
xlsb : com.microsoft.Excel.sheet.binary.macroenabled
csv : public.comma-separated-values-text
doc : com.microsoft.word.doc
docx : org.openxmlformats.wordprocessingml.document
docm : org.openxmlformats.wordprocessingml.document.macroenabled
ppt : com.microsoft.powerpoint.ppt
pptx : org.openxmlformats.presentationml.presentation
pptm : org.openxmlformats.presentationml.presentation.macroenabled
txt : public.plain-text
pdf : com.adobe.pdf
jpg : public.jpeg
png : public.png
QIF : com.apple.traditional-mac-plain-text
htm : public.html
If you need to know another format send me a mail and I try to help you.
Example for Excel for Windows
Note: The macro example for Windows and Mac are both using this function to test if the file is open.
In Windows you can use filefilter to only display the files you want and use MultiSelect to select more then one file. Also it is possible with ChDrive and ChDir to set the folder that is selected when GetOpenFilename opens, see basic Windows example below :
When you want to add more filters you can use this in the Windows macro to filter on xls and csv files
This Excel tutorial explains how to open the Visual Basic Editor in Excel 2011 for Mac (with screenshots and step-by-step instructions).
See solution in other versions of Excel:
You can access the VBA environment in Excel 2011 for Mac by opening the Visual Basic editor.
First, be sure that the Developer tab is visible in the toolbar in Excel.
The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form Controls like buttons, checkboxes, etc.
To display the Developer tab, click on Preferences under the Excel menu at the top of the screen.
When the Excel Preferences window appears, click on the Ribbon icon in the Sharing and Privacy section.
In the Customize section, check Developer in the list of tabs to show. Then click on the OK button.
Select the Developer tab from the toolbar at the top of the screen. Then click on the Editor option in the Visual Basic group.
Vba On Mac
Now the Microsoft Visual Basic editor should appear and you can view your VBA code.