Copy file | Mac Excel Automation

Copy File or files with VBA in Mac Excel

Note : When you use VBA in Mac Excel 2016 or higher that is working with files and folders you will notice that it is possible that it will ask you permission to access the file or folder (Grant File Access Prompt), this is because of Apple’s sandbox requirements.This means that when you want to save/open files or check if it exists with VBA code the first time you will be prompted to allow access on the first attempt to access such a folder or file. If you want to avoid problems like this read this page : Problems with Apple’s sandbox requirements

Note: In Excel for Windows we have more ways to copy files, but on the Mac for example FSO is not working:
Set FSO = CreateObject("scripting.filesystemobject")

VBA FileCopy and Name

But we can use VBA FileCopy and Name in Mac Office to copy files like this :
Note: that if you have file names with accented words in it, VBA FileCopy and Name are not working on a Mac. Check out the second part of this page for a solution that use AppleScriptTask.

VBA SaveCopyAs

If you want to make a copy/backup of the ActiveWorkbook you can also use SaveCopyAs but this is only working if the file is open.

Sub SaveCopyAs_Example()
    'This example save a copy of the ActiveWorkbook like it is on that moment.
    'This macro make it easy way to backup the file when you want

    'Check if the file have a path
    If ActiveWorkbook.Path = vbNullString Then
        MsgBox "The ActiveWorkbook have no file path, save it first and try again"
        Exit Sub
    End If

    'Set a reference to the ActiveWorkbook
    Set wb = ActiveWorkbook

    'Set the Save location
    'I use the same folder as the ActiveWorkbook in this example
    SaveLocation = wb.Path & Application.PathSeparator
    
    'Enter the file name without the extension
    'I use the Date/Time in the file name in this example
    SaveCopyAsFileName = "Copy of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

    'Get the file extension of the activeworkbook
    FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1)))

    'Save the copy of the ActiveWorkbook
    wb.SaveCopyAs SaveLocation & SaveCopyAsFileName & FileExtStr

    MsgBox "You find the copy of the file in this folder: " & SaveLocation
End Sub

Use VBA AppleScriptTask to avoid problems with file names with accented words

Microsoft added a new VB command "AppleScriptTask" that accesses and runs an AppleScript file located outside the sandboxed app. This new approach is not as convenient: with the VBA MacScript function you could have the script in the file itself, while with the AppleScriptTask method you need to distribute an extra file containing the script, and it must be placed in the specified location on the user’s system to have permission to run. This requires some user interaction the first time.

I create an install file to copy the script file in the correct location for you to make this easy.
Download the folder MacCopyFile with the script file named 'CopyFile.scpt' and the 'Install.scpt' file that you must Run one time to copy the Copyfile.scpt file in the correct location..

Open the MacCopyFile folder in your Downloads folder and Double Click the Install.scpt file to open it in the Mac Script Editor. After you open it Press the Run button or use the shortcut Command r to Run the script. It will first ask you to close Excel and after it say it is ready you can close the Script Editor, and you can call the script with the test macro below. You can delete the MacCopyFile folder now in your downloads folder.

See the VBA Macro example below that you can use to copy or copy/rename, you must change two lines in this tester before you can run it. It will test if the SourceFile exist in the script but not test if the DestinationFile folder exist, you can add that yourself if you want if that is important.

SourceFile = "/Users/rondebruin/Desktop/Lumière.png"
DestinationFile = "/Users/rondebruin/Desktop/Export/Lumière.png"

If you have for example a list like this in a worksheet with in B the file folder path and in C the file name that you want to copy and in D the new file name you can try the example below to make it easy to copy the files in a loop. Note: This example point to the data on the ActiveSheet, you can change this the way you want it.

Sub LoopToCopyFiles()
'Ron de Bruin, 1-Sept-2024
Dim ExportPath As String
Dim Folder As String
Dim LastRow As Long
Dim I As Long
Dim FilePath As String
Dim MyScript As String

'Enter the Folder path where you want to save the files
ExportPath = "/Users/rondebruin/Desktop/Lumière/"
    
    'Check if the pathSeparator at the end is there, and if not add it
    If Not Right(ExportPath, 1) = Application.PathSeparator Then
        ExportPath = ExportPath & Application.PathSeparator
    End If
   
'Test if this folder exists
If AppleScriptTask("CopyFile.scpt", "ExistsFolder", ExportPath) Then

'We assume in this example that the data is on the activesheet
With ActiveSheet
        'Get the lastrow with a file path in B
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        'Loop from row 2 till the last row with data in B
        For I = 2 To LastRow
            'Make the File path from the values in B and C
            FilePath = .Cells(I, "B").Value & Application.PathSeparator & .Cells(I, "C").Value
            'Check if FilePath exists
            If AppleScriptTask("CopyFile.scpt", "ExistsFile", FilePath) Then
            'If D have a new file name copy the file
                If .Cells(I, "D").Value <> "" Then
                    MyScript = AppleScriptTask("CopyFile.scpt", "CopyFileRename", FilePath & ";" & ExportPath & .Cells(I, "D").Value)
                End If
            End If
        Next I
End With
    MsgBox "Check out the Export folder with the new files: " & ExportPath
Else
    MsgBox "The ExportPath not exists, please create it manual or add code to make the folder instead of this msgbox"
End If
End Sub

Note: Check out also my FileLister add-in to get a list very easy of all files in a folder or use a VBA macro like this one to get the list of files.