Kill (delete) didn't work

[sassriverrat]

Good Evening,

I've missed something I guess (and I know my code could probably be a lot more efficient) because this code below is intended to take a file, save it under a new name (as specified from the address and new names in the workbook) and then delete the old one.

Ideas what went wrong?

Private Sub SaveAsDirectory()'Creates the SaveAs "#L/B and Ports" on the Arrival Sheet


    Dim Path1 As String
    Dim Path2 As String
    Dim Path3 As String
    Dim Path4 As String
    Dim Path5 As String
    Dim path6 As String
    Dim Path7 As String
    Dim Path8 As String
    Dim myfilename As String
    Dim fpathname As String
    Dim oldpathme As String
    Dim int1 As Integer:
    Dim int2 As Integer:
    Dim path As String:
    Dim x As Integer:
    Dim fldr As String:
    resp As Integer
    Path1 = Worksheets("Notes").Range("O26")
    Path2 = Worksheets("Notes").Range("P26")
    Path3 = Worksheets("Notes").Range("Q26")
    Path4 = Worksheets("Notes").Range("R26")
    Path5 = Worksheets("Notes").Range("S26")
    Path7 = Worksheets("Notes").Range("O17")
    Path8 = Worksheets("Notes").Range("O19")
    int1 = Worksheets("Notes").Range("T23")
    int2 = Worksheets("Notes").Range("O26")
    path = Worksheets("Notes").Range("N22")
    x = Int((int2 - 1) / int1) * int1
    fldr = 1 + x & "-" & int1 + x
    
    myfilename = Path1 & Path2 & " " & Path3 & Path4 & Path5
    fpathname = path & "\" & fldr & "\" & myfilename & ".xlsm"
    oldpathme = Path7 & "\" & Path8 & ".xlsm"
    
    ActiveSheet.EnableCalculation = False
    
    If ActiveWorkbook.Name = oldpathme Then
    resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
        If resp = vbYes Then
        If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
        ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    End If


    'Call File Killer
    Kill (oldpathme)
    
    Application Closer
    If Workbooks.Count > 1 Then
       ActiveWorkbook.Close
    Else: Application.Quit
    End If
    
    ElseIf ActiveWorkbook.Name = fpathname Then
        ActiveWorkbook.Save
           'Application Closer
        If Workbooks.Count > 1 Then
        ActiveWorkbook.Close
        Else: Application.Quit
        End If
    End If
End Sub

 

[Joe4]

Your oldpathme calculation does not look quite right. Verify its contents before using Kill with it. You can do that easily with a MsgBox.

    'Call File Killer
    MsgBox oldpathme
    Kill (oldpathme)

I am guessing once you see what the value is, it will be evident what the problem is.

 

[sassriverrat]

I adjusted a few and added some names. Let me know what you think

Private Sub SaveAsDirectory()
'Creates the SaveAs "#L/B and Ports" on the Arrival Sheet


    Dim Path1 As String
    Dim Path2 As String
    Dim Path3 As String
    Dim Path4 As String
    Dim Path5 As String
    Dim path6 As String
    Dim Path7 As String
    Dim Path8 As String
    Dim myfilename As String
    Dim fpathname As String
    Dim oldpathme As String
    Dim int1 As Integer:
    Dim int2 As Integer:
    Dim path As String:
    Dim x As Integer:
    Dim fldr As String:
    Dim resp As Integer
    With Worksheets("Notes")
    Path1 = .Range("O26") '#
    Path2 = .Range("P26") 'L/B
    Path3 = .Range("Q26") 'Dep Port
    Path4 = .Range("R26") '-
    Path5 = .Range("S26") 'Arr Port
    Path7 = .Range("O17") 'SaveAs Now Directory
    Path8 = .Range("O19") 'Current Voyage Report
    int1 = .Range("T23") 'increments
    int2 = .Range("O26") '#
    path = .Range("N22") 'SaveAs Archive Directory
    End With
    x = Int((int2 - 1) / int1) * int1
    fldr = 1 + x & "-" & int1 + x
    
    myfilename = Path1 & Path2 & " " & Path3 & Path4 & Path5
    fpathname = path & "\" & fldr & "\" & myfilename & ".xlsm"
    oldpathme = Path7 & "\" & Path8 & ".xlsm"
    oldnameme = Path8 & ".xlsm"
    
    ActiveSheet.EnableCalculation = False
    
    
    If ActiveWorkbook.Name = oldnameme Then
        resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
            If resp = vbYes Then
            If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
            ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            End If
            End If
        'Call File Killer
            Kill (oldpathme)
        'Application Closer
            If Workbooks.Count > 1 Then
            ActiveWorkbook.Close
            Else: Application.Quit
            End If
    'Debug names
    Debug.Print ActiveWorkbook.Name
    
    ElseIf ActiveWorkbook.Name = myfilename & ".xlsm" Then
        ActiveWorkbook.Save
        'Application Closer
            If Workbooks.Count > 1 Then
            ActiveWorkbook.Close
            Else: Application.Quit
            End If
    ElseIf ActiveWorkbook.Name = "Master Voyage Report" & ".xlsm" Then
       resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
           If resp = vbYes Then
            If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
            ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            End If
            
        'Application Closer
            If Workbooks.Count > 1 Then
            ActiveWorkbook.Close
            Else: Application.Quit
            End If
    End If
End Sub

 

[Joe4]

Did you try what I suggested?
What is "oldpathme" returning?

 

[sassriverrat]

Yessir I did! Thank you very much. So I made a few other big modifications, code below if you want to see it.
Namely, I needed to make oldpathme be defined after oldnameme to ensure it was fully defined instead of just giving me a "/" as my answer. That debugger.print makes a big difference!
Thanks!
I also needed a End If's removed

Private Sub SaveAsDirectory()'Creates the SaveAs "#L/B and Ports" on the Arrival Sheet


    Dim Path1 As String
    Dim Path2 As String
    Dim Path3 As String
    Dim Path4 As String
    Dim Path5 As String
    Dim path6 As String
    Dim Path7 As String
    Dim Path8 As String
    Dim myfilename As String
    Dim fpathname As String
    Dim oldpathme As String
    Dim oldnameme As String
    Dim int1 As Integer:
    Dim int2 As Integer:
    Dim path As String:
    Dim x As Integer:
    Dim fldr As String:
    Dim resp As Integer
    With Worksheets("Notes")
    Path1 = .Range("O26") '#
    Path2 = .Range("P26") 'L/B
    Path3 = .Range("Q26") 'Dep Port
    Path4 = .Range("R26") '-
    Path5 = .Range("S26") 'Arr Port
    Path7 = .Range("N17") 'SaveAs Now Directory
    Path8 = .Range("O19") 'Current Voyage Report
    int1 = .Range("T23") 'increments
    int2 = .Range("O26") '#
    path = .Range("N22") 'SaveAs Archive Directory
    End With
    x = Int((int2 - 1) / int1) * int1
    fldr = 1 + x & "-" & int1 + x
    
    myfilename = Path1 & Path2 & " " & Path3 & Path4 & Path5 & ".xlsm"
    fpathname = path & "\" & fldr & "\" & myfilename
    oldnameme = Path8 & ".xlsm"
    oldpathme = Path7 & "\" & oldnameme
    
    ActiveSheet.EnableCalculation = False
    
    If ActiveWorkbook.Name = oldnameme Then
        resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
            If resp = vbYes Then
                If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
                    ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            'Call File Killer
                Kill (oldpathme)
            'Application Closer
                If Workbooks.Count > 1 Then
                    ActiveWorkbook.Close
                Else: Application.Quit
                End If
            ElseIf resp = vbNo Then
                Exit Sub
            ElseIf resp = vbCancel Then
                Exit Sub
            End If
            'Debug names
            Debug.Print ActiveWorkbook.Name
    ElseIf ActiveWorkbook.Name = myfilename Then
        ActiveWorkbook.Save
        'Application Closer
            If Workbooks.Count > 1 Then
                ActiveWorkbook.Close
            Else: Application.Quit
            End If
    ElseIf ActiveWorkbook.Name = "Master Voyage Report" & ".xlsm" Then
       resp = MsgBox("You are trying to save voyage " & myfilename & " to:" & vbCrLf & fpathname & vbCrLf & vbCrLf & "Current Voyage Report will be archived and the Master Voyage Report reset for next voyage. Thanks for using the OSG Voyage Reporting System!" & vbCrLf & vbCrLf & "File: " & int2 & vbTab & "Folder: " & fldr & vbCr & path & "\" & fldr, vbYesNo)
           If resp = vbYes Then
                If Len(Dir(path & "\" & fldr, vbDirectory)) = 0 Then MkDir path & "\" & fldr
                    ActiveWorkbook.SaveAs Filename:=fpathname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            'Application Closer
                If Workbooks.Count > 1 Then
                    ActiveWorkbook.Close
                Else: Application.Quit
                End If
            ElseIf resp = vbNo Then
                Exit Sub
            ElseIf resp = vbCancel Then
                Exit Sub
            End If
    End If
End Sub

 

[Joe4]

You are welcome!

I use MsgBox in debugging quite often. It is great way to easily check/see what is being returned when things are working as expected.