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: I have also examples to copy a file or folder on this page but that is not so easy to setup but if you want to know more visit this page: How to use AppleScriptTask in Mac Office

In Excel for Windows we have more ways to copy files but on the Mac FSO is not working for example:
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 :


Sub Copy_One_File()
'You can also rename the file if you want
    FileCopy "/Users/rondebruin/Desktop/xlsx/File1.xlsx", "/Users/rondebruin/Desktop/Export/File1.xlsx"
End Sub

Sub Move_One_File()
'You can also rename the file if you want
    Name "/Users/rondebruin/Desktop/xlsx/File1.xlsx" As "/Users/rondebruin/Desktop/Export/File1.xlsx"
End Sub

If you have for example a list like this in a worksheet with in B the file 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 more files.


Sub LoopToCopyFiles()
Dim ExportPath As String
Dim LastRow As Long
Dim I As Long
Dim FilePath As String

'Folder where you want to save the files
ExportPath = "/Users/rondebruin/Desktop/Export/"

With ActiveSheet
        'Get the lastrow with a file path inB
        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
            
            'If D have a new file name copy the file
            If .Cells(I, "D").Value <> "" Then FileCopy FilePath, ExportPath & .Cells(I, "D").Value
        Next I
End With

MsgBox "Check out the Export folder : " & ExportPath
End Sub

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