Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Copy Folder in Microsoft Office for the Mac with VBA

Below are two examples to copy a complete folder to another folder on your Mac. You can use it for example to make a backup. Note: The second macro will rename the folder with a Date/Time stamp.

Both macros use the function FileOrFolderExistsOnMac that you find below the two macros.

Note : This is not working in Mac Office 2016 on this moment

Macro's and function

Before you run one of the macros you must change the path to the folder you want to copy and the destination folder where the code will paste the complete folder.

 FolderToDuplcate = MacScript("return (path to desktop folder) as string") & "MyFolder:"
DestinationFolder = MacScript("return (path to desktop folder) as string") & "DestFolder:"

In the examples I use two folders on my Desktop but you can also hard code the path or add code to browse to a folder if you want. See this page for more Special Folders.

 

Sub CopyFolder()
'Code to copy a folder in Office for the Mac and replace it if it already exists
'Note: This macro use the function FileOrFolderExistsOnMac
'Ron de Bruin, 20-April-2013
    Dim FolderToDuplicate As String
    Dim DestinationFolder As String
    Dim ScriptToDuplicateFolder As String

    'Enter the path of the folder to duplicate and the destination folder
    'In the example below both folders are on the desktop
    FolderToDuplicate = MacScript("return (path to desktop folder) as string") & "MyFolder:"
    DestinationFolder = MacScript("return (path to desktop folder) as string") & "DestFolder:"

    'Or enter the complete path
    'FolderToDuplicate = "Macintosh HD:Users:YourUserName:Desktop:MyFolder:"
    'DestinationFolder = "Macintosh HD:Users:YourUserName:Desktop:DestFolder:"


    'Test if both the folders exists
    If FileOrFolderExistsOnMac(2, FolderToDuplicate) = False Or _
       FileOrFolderExistsOnMac(2, DestinationFolder) = False Then
        MsgBox "Sorry the folder to duplicate or the destination folder not exist"
        Exit Sub
    End If

    'Build the script string
    ScriptToDuplicateFolder = "tell application " & Chr(34) & _
                              "Finder" & Chr(34) & Chr(13)
    ScriptToDuplicateFolder = ScriptToDuplicateFolder & "set duplicatedFolder to duplicate folder " & _
                              Chr(34) & FolderToDuplicate & Chr(34) & " to folder " & Chr(34) & _
                              DestinationFolder & Chr(34) & " with replacing" & Chr(13)
    ScriptToDuplicateFolder = ScriptToDuplicateFolder & "end tell"

    'Run the script
    On Error Resume Next
    MacScript (ScriptToDuplicateFolder)
    On Error GoTo 0
End Sub


Sub CopyFolder2()
'Copy a folder in Office for the Mac and replace it if it already exists and rename it with the Date/Time
'Note: This macro use the function FileOrFolderExistsOnMac
'Ron de Bruin, 20-April-2013
    Dim FolderToDuplicate As String
    Dim DestinationFolder As String
    Dim ScriptToDuplicateFolder As String

    'Enter the path of the folder to duplicate and the destination folder
    'In the example below both folders are on the desktop
    FolderToDuplicate = MacScript("return (path to desktop folder) as string") & "MyFolder:"
    DestinationFolder = MacScript("return (path to desktop folder) as string") & "DestFolder:"

    'Or enter the complete path
    'FolderToDuplicate = "Macintosh HD:Users:YourUserName:Desktop:MyFolder:"
    'DestinationFolder = "Macintosh HD:Users:YourUserName:Desktop:DestFolder:"

    'Test if both the folders exists
    If FileOrFolderExistsOnMac(2, FolderToDuplicate) = False Or _
       FileOrFolderExistsOnMac(2, DestinationFolder) = False Then
        MsgBox "Sorry the folder to duplicate or the destination folder not exist"
        Exit Sub
    End If

    'Build the script string
    ScriptToDuplicateFolder = "tell application " & Chr(34) & _
                              "Finder" & Chr(34) & Chr(13)
    ScriptToDuplicateFolder = ScriptToDuplicateFolder & "set duplicatedFolder to duplicate folder " & _
                              Chr(34) & FolderToDuplicate & Chr(34) & " to folder " & Chr(34) & _
                              DestinationFolder & Chr(34) & " with replacing" & Chr(13)
    ScriptToDuplicateFolder = ScriptToDuplicateFolder & _
                              "set name of duplicatedFolder to " & Chr(34) & _
                              Format(Now, "dd-mmm-yy h-mm-ss") & Chr(34) & Chr(13)
    ScriptToDuplicateFolder = ScriptToDuplicateFolder & "end tell"

    'Run the script
    On Error Resume Next
    MacScript (ScriptToDuplicateFolder)
    On Error GoTo 0
End Sub


Function FileOrFolderExistsOnMac(FileOrFolder As Long, FileOrFolderstr As String) As Boolean
'By Ron de Bruin, 30-July-2012
'First argument 1 is check file and 2 is check folder
'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