0

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
MBrann
  • 223
  • 5
  • 23
  • Have you tested to see if the sFile string evaluates to exactly the path/file name? you can use Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) to see if the string matches your string exactly – Benjamin Goldwater Aug 30 '17 at 14:40
  • Hi @BenjaminGoldwater yes it appears to match perfectly – MBrann Aug 30 '17 at 14:45
  • I am not able to recreate the error. When I run the first 11 lines, I am able to identify a file and find that it exists. You could try replacing.Text with .Value or .Value2. .Text gives you a string representing what is displayed on the screen for the cell, so you could get "####". – Benjamin Goldwater Aug 30 '17 at 14:58
  • see https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 – Benjamin Goldwater Aug 30 '17 at 14:59

0 Answers0