Alternative method to delete file with out having to add MS Scripting Runtime Reference

Question

The answer to this question states that to avoid the error you need to add the reference to MS Scripting Run-time.

I have used this methodology to delete a file in my code, but since I know it will be used on several computer which will not have that reference included, I figure I have two choices:

1. Use a different methodology to delete an existing file.
2. Figure out someway to load the reference file when the file is opened.

This sub is tied to a ActiveX Button on the sheet

Sub Createbackupfile()

Dim strCurrentName As String
Dim strCurrentPathAndName As String
Dim strCurrentPath As String
Dim strPreviousFilename As String

    strCurrentName = ThisWorkbook.Name
    strCurrentPathAndName = ThisWorkbook.FullName
    strCurrentPath = Replace(strCurrentPathAndName, strCurrentName, "")
    
    NewPath = "OldForecasts\"
    
    With ThisWorkbook
        .SaveAs Filename:=strCurrentPath & StrNewPath & strCurrentName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End With
    
    With New FileSystemObject
        If .FileExists(CurrentPathAndName) Then
            .deleteFile CurrentPathAndName
        End If
    End With
    
End Sub

Question

What is an an alternative approach to using New FileSytemObject

Answer 1

Several things to mention:

(1) A small thing: You can access the folder of a workbook by using ThisWorkbook.Path (note that this doesn't contain the trailing backslash). So use strCurrentPath = ThisWorkbook.Path

(2) You assign the subfolder name into a variable named NewPath but later you access strNewPath. Same problem with CurrentPathAndName vs strCurrentPathAndName. Avoid such errors by always using Option Explicit.

Also, unless in your code you plan to have different subfolders as backup folder, declare the new path as constant, not as variable:

Const strNewPath = "OldForecasts"

(3) As mentioned in the comments, there are some (rather old) commands for file handling in VBA:

kill to delete a file
Dir to check if a file or folder exists. To check for folders, you need to add vbDirectory as 2nd argument to the Dir-command.
MkDir to create a folder.

Code could look like this:

Option Explicit

Sub Createbackupfile()

    Const BackupFolder = "OldForecasts"

    Dim CurrentName As String
    Dim CurrentPathAndName As String
    Dim CurrentPath As String

    With ThisWorkbook
        CurrentName = .Name
        CurrentPathAndName = .FullName
        CurrentPath = .Name

        Dim newPath As String
        newPath = CurrentPath & "\" & BackupFolder
        If Dir(newPath, vbDirectory) = "" Then MkDir newPath
        .SaveAs Filename:=newPath & "\" & CurrentName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        Kill CurrentPathAndName
    End With
End Sub

Note that if this code should run also on a Mac, you need to replace the backslash with a slash - or use Application.PathSeparator instead:

newPath = CurrentPath & Application.PathSeparator & BackupFolder

(as far as I know, the commands itself work on a Mac, but I cannot check this as I don't have a Mac to test.)

(4) The FileSystemObject of the Scripting Runtime is an alternative way to access file and folder information. It has some advantages over the old commands (for example you can recursively walk through your folders and subfolders) and offers additional commands.

The Scripting Runtime is present on all windows computers (but not on any Mac). Adding a reference to your project just tells the VBA compiler you want to use it (this is called early binding). Advantage is that the compiler can check the syntax for you already on compile time, and you have intellisense as support when typing code.

However, you can perfectly live without early binding by using CreateObject. This is called late binding. The compiler does not know anything about the object that is created, and if you access any property or method, the VBA runtime will look at execution time if that exists. Example:

' Early binding (your current code)
With New FileSystemObject
    If .FileExists(CurrentPathAndName) Then
        .DeleteFile CurrentPathAndName
    End If
End With

The VBA editor can help you with intellisense and the compiler checks if methods FileExists and DeleteFile exists. If you have a typo (you type for example FileExist), the compiler can already warn you before the code runs.

' Late binding (no reference needed) 
With CreateObject("Scripting.FileSystemObject")
    If .FileExists(CurrentPathAndName) Then
        .DeleteFile CurrentPathAndName
    End If
End With

Now the compiler does not know anything about the object that is created and you will get a runtime error when using an invalid method name.

Both versions will work on any computer where the library exists and will not work on any computer where the library does not exists. However, when the library does not exist and you use early binding, the code will not compile and you can't use any of the code. With late binding, you can use any other code of your project.