Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Use VBA Kill to delete files

Note: In computers running Windows, you can use the VBA Kill command to delete files from your system. However, on a Mac, the Kill command will not work correctly in 2011 with long file names (max of 32 with the extension). This is fixed in the new Mac Office version. The following two code examples that do both the same demonstrates a way that is working in Excel 2011 and up. Note: you see that it is working with the old paths from 2011 with : as seperator and also with the new posix paths that will be used in the new Mac Office 2016.

Sub KillFile1()
'Call the KillFileOnMac function to delete the file from a Mac.
    KillFileOnMac "YosemiteLacie256:Users:rondebruin:Desktop:testscript.xlsm"
End Sub

Sub KillFile2()
'Call the KillFileOnMac function to delete the file from a Mac.
    KillFileOnMac "/Users/rondebruin/Desktop/testscript.xlsm"
End Sub


Function KillFileOnMac(Filestr As String)
    Dim ScriptToKillFile As String
    Dim Fstr As String
    'Ron de Bruin, 25-June-2015
    'Delete files from a Mac. Uses AppleScript to avoid
    'the problem with long file names in Office 2011
    If Val(Application.Version) < 15 Then
        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
    Else
        Fstr = MacScript("return POSIX path of (" & _
                         Chr(34) & Filestr & Chr(34) & ")")
        On Error Resume Next
        Kill Fstr
    End If
End Function

Important for Excel 2016, read this page :

Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code

On this page you find information for Excel for Windows
http://www.rondebruin.nl/win/s4/win004.htm