Delete pdf didnt happen & no RTE was shown

[ipbr21054]

Morning,

Could you please advise.
I am using the below code & all working fine THEN i added to it but have an issue.
The added code is shown in RED

Sometimes when im mobile i generate a PDF file for the customer.
When back at my workplace i print off the proper file for my records & its then i wish to delete the generated pdf,hence the new line of code.

So i see the message shown in BLUE below THEN i expect the code to look at the name in cell G13 go to the path shown & DELETE IT.
Then continue with the rest of the code.

Now everything works as it should & i see no RTE messsages BUT the generated file didnt get deleted

Private Sub Print_Invoice_Click()
  Dim sPath As String, strFileName As String
  Dim MyFile As String
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select
    
    Exit Sub
  End If
  
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("L4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
  Exit Sub
  
  End If
    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    
  End With
  
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
  strFileName = sPath & Range("L4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
  MsgBox "ONCE PRINTED PLEASE CLICK THE OK BUTTON" & vbNewLine & vbNewLine & "TO SAVE INVOICE " & Range("L4").Value & " THEN TO CLEAR CURRENT INFO & GENERATED PDF ", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
  MyFile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR SCREEN SHOT PDF\" & Range("G13").Value & ".pdf"
    Dim i As Long, lRow As Long, ws As Worksheet
    Set ws = Application.Worksheets("DATABASE")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 6 To lRow
        If Trim(Range("G13").Value) = Trim(ws.Cells(i, 1).Value) Then

            If ws.Cells(i, 16).Value = "" Then
                ws.Cells(i, 16).Value = Range("L4").Value  ' adding invoice number to INV sheet "P"
                ActiveSheet.Hyperlinks.Add ws.Cells(i, 16), Address:="C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
                MsgBox "INVOICE " & ws.Cells(i, 16).Value & " WAS HYPERLINKED SUCCESSFULLY.", vbInformation, "HYPERLINK SUCCESSFULL MESSAGE"
            Else
                If MsgBox("COLUMN CELL P ISNT EMPTY " & ws.Cells(i, 16).Value & " IS ENTERED IN IT." & vbNewLine & "WOULD YOU LIKE TO CORRECT IT ?", vbCritical + vbYesNo, "COLUMN P NOT EMPTY MESSAGE") = vbYes Then
                    ws.Activate
                    ws.Cells(i, 16).Select
                End If
                Exit Sub
            End If
        End If
        
    Next i
    
    Range("G14:G18").ClearContents
    Range("L14:L18").ClearContents
    Range("G27:L36").ClearContents
    Range("G46:G50").ClearContents
    Range("L4").Value = Range("L4").Value + 1
    Range("G13").ClearContents
    Range("G13").Select
    
    Call PasteIfFormulas_Click
    
    ActiveWorkbook.Save

End Sub

 

[HaHoBe]

Hi Ian,

you assign a string to the variable in question in codeline

  MyFile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR SCREEN SHOT PDF\" & Range("G13").Value & ".pdf"

There is no other place in the code you presented where the variable is used except when dimming it.

If you want to delete the file you should add the codelines

  MyFile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR SCREEN SHOT PDF\" & Range("G13").Value & ".pdf"
  If Dir(MyFile) <> "" Then
    Kill MyFile
    MsgBox "Deleted file " & MyFile, vbInformation, "File deleted"
  End If

Check first that the file in question is available and then delete it (without placing it in the recycle bin).

HTH
Holger

 

[ipbr21054]

The text you have written under the code I left out you mentioned , without placing it in the recycle bin.
I assume Kill Myfile does this.

How would the code be written should I need a fall back & need to retrieve it ?

 

[HaHoBe]

Hi Ian,

the request to make the file appear in the recycle bin would need some API as far as I know and could look like

Private Declare PtrSafe Function SHFileOperationA Lib "shell32.dll" ( _
      ByRef lpFileOp As SHFILEOPSTRUCT) As Long

Private Type SHFILEOPSTRUCT
  hwnd As LongPtr
  wFunc As Long
  pFrom As String
  pTo As String
  fFlags As Integer
  fAnyOperationsAborted As Long
  hNameMappings As LongPtr
  lpszProgressTitle As String
End Type

Private Const FO_DELETE = &H3&
Private Const FOF_ALLOWUNDO = &H40&
Private Const FOF_NOCONFIRMATION = &H10
'

Private Sub Print_Invoice_Click()
  Dim sPath As String, strFilename As String
  Dim MyFile As String
  strFilename = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select
    Exit Sub
  End If
  
  strFilename = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Dir(strFilename) <> vbNullString Then
    MsgBox "INVOICE " & Range("L4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
    Exit Sub
  End If
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFilename, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
  End With
  
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
  strFilename = sPath & Range("L4").Value & ".pdf"
  If Dir(strFilename) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFilename
  End If
  
  MsgBox "ONCE PRINTED PLEASE CLICK THE OK BUTTON" & vbNewLine & vbNewLine & "TO SAVE INVOICE " & Range("L4").Value & " THEN TO CLEAR CURRENT INFO & GENERATED PDF ", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
  MyFile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR SCREEN SHOT PDF\" & Range("G13").Value & ".pdf"
  If Dir(MyFile) <> "" Then
'    Kill MyFile
    Call Move_to_Recycling_Bin(MyFile) 'Datei löschen
    MsgBox "Deleted file " & MyFile, vbInformation, "File deleted"
  End If
  
  Dim i As Long, lRow As Long, ws As Worksheet
  Set ws = Application.Worksheets("DATABASE")
  lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
  For i = 6 To lRow
    If Trim(Range("G13").Value) = Trim(ws.Cells(i, 1).Value) Then
    
      If ws.Cells(i, 16).Value = "" Then
        ws.Cells(i, 16).Value = Range("L4").Value  ' adding invoice number to INV sheet "P"
        ActiveSheet.Hyperlinks.Add ws.Cells(i, 16), Address:="C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
        MsgBox "INVOICE " & ws.Cells(i, 16).Value & " WAS HYPERLINKED SUCCESSFULLY.", vbInformation, "HYPERLINK SUCCESSFULL MESSAGE"
      Else
        If MsgBox("COLUMN CELL P ISNT EMPTY " & ws.Cells(i, 16).Value & " IS ENTERED IN IT." & vbNewLine & "WOULD YOU LIKE TO CORRECT IT ?", vbCritical + vbYesNo, "COLUMN P NOT EMPTY MESSAGE") = vbYes Then
          ws.Activate
          ws.Cells(i, 16).Select
        End If
        Exit Sub
      End If
    End If
  Next i
  
  Range("G14:G18").ClearContents
  Range("L14:L18").ClearContents
  Range("G27:L36").ClearContents
  Range("G46:G50").ClearContents
  Range("L4").Value = Range("L4").Value + 1
  Range("G13").ClearContents
  Range("G13").Select
  
  Call PasteIfFormulas_Click
  
  ActiveWorkbook.Save

End Sub

Private Sub Move_to_Recycling_Bin(strFilename As String)
Dim udtFileStructure As SHFILEOPSTRUCT
With udtFileStructure
  .wFunc = FO_DELETE
  .pFrom = strFilename
  .fFlags = FOF_ALLOWUNDO Or FOF_NOCONFIRMATION
End With
Call SHFileOperationA(udtFileStructure)
End Sub

To be honest: i would copy the file to another location (a folder named Safe for example) to keep it available instead of applying API to get the result wanted (show the file in the recycle bin).

Holger