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.

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 Install CopyFile 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 Install CopyFile 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 Install CopyFile 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.

FileCopy

06/12/2024
Web design by Will Woodgate