Export (0) Print
Expand All

Performing File and Folder Operations Programmatically in Excel 2011 for the Mac

Office 2010

Summary: Learn how to perform various file and folder operations with VBA by using Microsoft Excel 2011 for the Mac.

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.

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

  1. Start Excel. Click Tools, click Macro, and then click Visual Basic Editor to open the Visual Basic Editor.

    Note 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.

  2. On the Insert menu, click Module to add a general module to the VBA project in your test workbook.

  3. Paste or type the following macros and function into the module.

    Note 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 
    
    
  4. 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.

  5. 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.

In this section, you programmatically test whether a file is open.

To determine whether a file is open

  1. Open the Visual Basic Editor and add a standard module by using the steps specified in Testing Whether a File or Folder Exists.

  2. Paste or type the following macro and function into the module.

    Note 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
    
  3. Close the Visual Basic Editor and return to Excel.

  4. Run the macro.

In this section, you learn how to delete one or more files on the Mac programmatically.

To delete one or more files

  1. Open the Visual Basic Editor and add a standard module by using the steps specified in Testing Whether a File or Folder Exists.

  2. Paste or type the following macro and function into the module.

    Note 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.

    NoteNote

    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
    
  3. Close the Visual Basic Editor and return to Excel.

  4. Run the macro.

In this section, you learn how to determine whether a worksheet is present in a workbook.

To determine whether a worksheet exists

  1. Open the Visual Basic Editor and add a standard module by using the steps specified in Testing Whether a File or Folder Exists.

  2. Paste or type the following macro and function into the module.

    Note 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
    
  3. Close the Visual Basic Editor and return to Excel.

  4. Run the macro.

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

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.

For more information about the topics discussed in this article, see the following:

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.

Show:
© 2014 Microsoft