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

  • Artikel

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

Letzte Änderung: Mittwoch, 5. September 2012

Gilt für: Excel | Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

Inhalt dieses Artikels
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

Applies to:   Microsoft Excel 2011 for the Mac

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

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

    Hinweis

    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.

    Hinweis

    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.

Testing whether a File is Open

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.

    Hinweis

    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.

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

  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.

    Hinweis

    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.

    Hinweis

    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.

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

  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.

    Hinweis

    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.

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.