Deleting a File using VBA

6.3k views Asked by At

I currently have a code to delete files in a userform. I enter a reference and this in term populates to a sheet and all the calculations are worked out.

What I am trying to do is delete the old existing document and then save a new copy with the word "CLOSED" in the title. I feel this code should work but cannot see why it is not finding the specified file.

Sub CloseDoc()

Dim FSO
Dim sFile As String

sFile = "M:\Documents\" & Range("B3").Text & " - " & Range("B14").Text & ".xlsx"
Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FileExists(sFile) Then
    FSO.DeleteFile sFile, True
        MsgBox "Deleted The File Successfully, Ready to update with CLOSED information", vbInformation, "Done!"
    Else
        MsgBox "Specified File Not Found", vbInformation, "Not Found!"
End If

    Sheets("ITC").Copy

    Range("A1:B54").Select
        Application.CutCopyMode = False
        Selection.Copy
        ActiveWindow.SmallScroll Down:=-60
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Select
'Save Document to Dir
    ActiveWorkbook.SaveAs Filename:= _
        "M:\Documents\" & Range("B3").Text & " - " & Range("B14").Text & " - CLOSED" & " .xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close

    End Sub
0

There are 0 answers