Performing File and Folder Operations Programmatically in Excel 2011 for the Mac
Summary: Learn how to perform various file and folder operations with VBA by using Microsoft Excel 2011 for the Mac.
Applies to: Excel | Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA
In this article
Introduction to File and Folder Operations for the Apple Macintosh
Testing Whether a File or Folder Exists
Testing whether a File is Open
Deleting Files on a Macintosh
Testing Whether a Worksheet Exists
Run the Correct Macro in Windows or on the Macintosh
Conclusion
Additional Resources
About the Authors
Published: September 2012
Provided by: Ron de Bruin, Excel MVP | Frank Rice, Microsoft Corporation
Contents
Introduction to File and Folder Operations for the Apple Macintosh
Testing Whether a File or Folder Exists
Testing whether a File is Open
Deleting Files on a Macintosh
Testing Whether a Worksheet Exists
Run the Correct Macro in Windows or on the Macintosh
Conclusion
Additional Resources
About the Authors
Introduction to File and Folder Operations for the Apple Macintosh
As a Microsoft Excel developer, you must often test whether a worksheet, file or folder exists, or if a particular file is currently open. You might also have to delete a file. On a computer that is running Windows, you can use the File System Object (FSO) object to test whether a file or a folder exists. The FSO model provides an object-based tool for working with folders and files. Unfortunately, the FSO model is not available on the Apple Macintosh (Mac).
Another way to see the available files in a directory is to use the Directory (Dir) command. However, the Dir command on a Mac cannot display file names that exceed 32 characters, including the file name extension. To delete files, you can use the Microsoft Visual Basic for Applications (VBA) Kill function. However, similar to the Dir command, the Kill function has problems with longer file names on the Mac.
This article describes ways to work around these limitations by using AppleScript inside VBA functions.
Testing Whether a File or Folder Exists
In this section, you examine methods that you can use to test whether a file or a folder exists in a directory.
To determine whether a file or a folder exists
Start Excel. Click Tools, click Macro, and then click Visual Basic Editor to open the Visual Basic Editor.
Note
In Excel 2011, you access the Visual Basic Editor and run macros from the Developer tab. If the Developer tab is not already displayed, do the following. On the Menu bar, click Excel, click Preferences, and then click Ribbon. Select the Developer box and then click OK.
On the Insert menu, click Module to add a general module to the VBA project in your test workbook.
Paste or type the following macros and function into the module.
Note
In the following sample code, both macros call the FileOrFolderExistsOnMac function to test for a file or a folder. The first argument in the FileOrFolderExistsOnMac function specifies whether to test for a file or a folder. In this instance:
-
Argument value 1 = file
-
Argument value 2 = folder
Sub TestFile() 'First argument, 1 = file and 2 = folder. 'Note: This macro uses the FileOrFolderExistsOnMac function. If FileOrFolderExistsOnMac(1, "Macintosh HD:Users:<user name>:Documents:YourFileName.xlsx") = True Then MsgBox "File exists." Else MsgBox "File does not exist." End If End Sub Sub TestFolder() 'First argument, 1 = file and 2 = folder. 'Note: This macro uses the FileOrFolderExistsOnMac function. If FileOrFolderExistsOnMac(2, "Macintosh HD:Users:<user name>:Documents") = True Then MsgBox "Folder exists." Else MsgBox "Folder does not exist." End If End Sub Function FileOrFolderExistsOnMac(FileOrFolder As Long, FileOrFolderstr As String) As Boolean 'By Ron de Bruin '30-July-2012 'Function to test whether a file or folder exist on a Mac. 'Uses AppleScript to avoid the problem with long file names. Dim ScriptToCheckFileFolder As String ScriptToCheckFileFolder = "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13) If FileOrFolder = 1 Then ScriptToCheckFileFolder = ScriptToCheckFileFolder & "exists file " & _ Chr(34) & FileOrFolderstr & Chr(34) & Chr(13) Else ScriptToCheckFileFolder = ScriptToCheckFileFolder & "exists folder " & _ Chr(34) & FileOrFolderstr & Chr(34) & Chr(13) End If ScriptToCheckFileFolder = ScriptToCheckFileFolder & "end tell" & Chr(13) FileOrFolderExistsOnMac = MacScript(ScriptToCheckFileFolder) End Function
-
Press CMD + q, or, on the Excel menu, click Close and Return to Microsoft Excel to close the Visual Basic Editor and return to Excel.
To run the macro, on the Tools menu, click Macro, click Macros, and then double-click the TestFile or TestFolder macro name. You can also run the macro from the Developer tab by clicking Macros, and then double-clicking the name of the macro.
Testing whether a File is Open
In this section, you programmatically test whether a file is open.
To determine whether a file is open
Open the Visual Basic Editor and add a standard module by using the steps specified in Testing Whether a File or Folder Exists.
Paste or type the following macro and function into the module.
Note
The following code also works in any version of Excel for Windows. It tests whether YourFileName.xlsm is a part of the Application.Workbooks collection.
Sub TestIfWorkbookIsOpen() If bIsBookOpen_RB("YourFileName.xlsm") Then MsgBox "Workbook is open." Else MsgBox "Workbook is not open." End If End Sub Function bIsBookOpen_RB(ByRef szBookName As String) As Boolean 'Created by Rob Bovey On Error Resume Next bIsBookOpen_RB = Not (Application.Workbooks(szBookName) Is Nothing) End Function
Close the Visual Basic Editor and return to Excel.
Run the macro.
Deleting Files on a Macintosh
In this section, you learn how to delete one or more files on the Mac programmatically.
To delete one or more files
Open the Visual Basic Editor and add a standard module by using the steps specified in Testing Whether a File or Folder Exists.
Paste or type the following macro and function into the module.
Note
On computers that are running Windows, you can use the VBA Kill command to delete files from the system. However, on a Mac, the Kill command does not work correctly with long file names (max of 32 with the extension). The following code provides a workaround.
Sub KillFile() 'Call the KillFileOnMac function to delete the file from a Mac. KillFileOnMac "Macintosh HD:Users:<user name>:Documents:YourFileName.xlsx" End Sub Function KillFileOnMac(Filestr As String) 'Ron de Bruin '30-July-2012 'Delete files from a Mac. 'Uses AppleScript to avoid the problem with long file names Dim ScriptToKillFile As String ScriptToKillFile = "tell application " & Chr(34) & _ "Finder" & Chr(34) & Chr(13) ScriptToKillFile = ScriptToKillFile & _ "do shell script ""rm "" & quoted form of posix path of " & _ Chr(34) & Filestr & Chr(34) & Chr(13) ScriptToKillFile = ScriptToKillFile & "end tell" On Error Resume Next MacScript (ScriptToKillFile) On Error GoTo 0 End Function
The following code deletes every file in a folder named Test in your Documents folder.
Note
You can use any path of a folder inside the FolderWithFiles string. Also note the wildcard character (*) in the macro. You can use it to search for all files of a specific type; for example, *.pdf to delete only PDF files or *.docx to delete only Microsoft Word *.docx files.
Sub DeleteFilesInFolder() Dim FolderWithFiles As String Dim scriptToRun As String FolderWithFiles = MacScript("return (path to documents folder) as string") & "Test:" ' Or enter the full path to the folder ‘ FolderWithFiles = "Macintosh HD:Users:<user name>:Desktop:Test:" If Right(FolderWithFiles, 1) <> ":" Then FolderWithFiles = FolderWithFiles & ":" End If scriptToRun = scriptToRun & "tell application " & Chr(34) & _ "Finder" & Chr(34) & Chr(13) scriptToRun = scriptToRun & _ "do shell script ""rm "" & quoted form of posix path of " & _ Chr(34) & FolderWithFiles & """ & " & Chr(34) & "*" & Chr(34) & Chr(13) scriptToRun = scriptToRun & "end tell" On Error Resume Next MacScript (scriptToRun) On Error GoTo 0 End Sub
Close the Visual Basic Editor and return to Excel.
Run the macro.
Testing Whether a Worksheet Exists
In this section, you learn how to determine whether a worksheet is present in a workbook.
To determine whether a worksheet exists
Open the Visual Basic Editor and add a standard module by using the steps specified in Testing Whether a File or Folder Exists.
Paste or type the following macro and function into the module.
Note
The following code works in any Excel version for Windows too. Note ThisWorkbook as the second argument in the sample code. The argument indicates that the workbook that contains the running code is checked for the worksheet. If you remove the second argument, the code defaults to ThisWorkbook. You can change ThisWorkbook to a reference to another workbook that you create in your code or you can use Activeworkbook to test the sheets in the active workbook.
Sub TestIfSheetExists() If SheetExists("SheetNameToTest", ThisWorkbook) Then MsgBox "Sheet exists." Else MsgBox "Sheet does not exist." End If End Sub Function SheetExists(SName As String, _ Optional ByVal WB As Workbook) As Boolean 'Created by Chip Pearson On Error Resume Next If WB Is Nothing Then Set WB = ThisWorkbook SheetExists = CBool(Len(WB.Sheets(SName).Name)) End Function
Close the Visual Basic Editor and return to Excel.
Run the macro.
Run the Correct Macro in Windows or on the Macintosh
Suppose that you have created the VBA project and are ready to deploy it to a mix of Windows and Mac users. You can use one of the following macros to test for the operating system version, and then call the appropriate macro for that system.
Sub WINorMAC()
'Test the operating system.
If Not Application.OperatingSystem Like "*Mac*" Then
'Is a Windows user.
Call My_Windows_Macro
Else
'Is a Mac user so you need to test whether the product is Excel 2011 or later.
If Val(Application.Version) > 14 Then
Call My_Mac_Macro
End If
End If
End Sub
Sub WINorMAC_2()
'Test using conditional compiler constants.
#If Win32 Or Win64 Then
'Is a Windows user.
Call My_Windows_Macro
#Else
'Is a Mac user so you need to test whether the product is Excel 2011 or later.
If Val(Application.Version) > 14 Then
Call My_Mac_Macro
End If
#End If
End Sub
Conclusion
This article has several examples that show how to programmatically test whether a file, a folder, or a worksheet exists or if a file is already open. It also has examples that show how to delete one or more files on a Mac by using VBA code. The authors encourage you to experiment with these samples to see how you can use them in your own applications.
Additional Resources
For more information about the topics discussed in this article, see the following:
About the Authors
Ron de Bruin is an Excel Most Valuable Professional (MVP) and a frequent contributor to the newsgroups. For more information, see Ron's Excel Web page.
Frank Rice is a senior programming writer and frequent contributor to the Microsoft Office Developer Center.