Using Kill command in macro

[kstrick9]

The below macro to insert a picture into a cell comment is functioning properly. The only issue is that it makes a copy of the picture in the folder of the workbook file path. Deleting this picture does not affect the inserted picture as a cell comment. How could the Kill command (or any other command) be used to delete this unneeded picture at the close of the macro? Thanks.

Option Explicit
Sub PictureIntoComment()
'Inserts picture into cell comment. Click active cell, then click picture, run macro.
Dim ch As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim ws As Worksheet
Dim sName As String
Dim cmt As Comment
Dim sPath As String
Dim sFile As String
Dim rng As Range
Set ws = ActiveSheet
Set rng = ActiveCell
sPath = ThisWorkbook.Path & "\"
sName = InputBox("Name for picture file (no extension)", "File Name")
If sName = "" Then sName = "Picture_" & Format(Date, "ddmmyy")
sFile = sPath & sName & ".jpg"
    dWidth = Selection.Width
    dHeight = Selection.Height
    
    ws.Unprotect "123"
    
    Selection.Cut
    Set ch = ws.ChartObjects.Add(Left:=rng.Left, Top:=rng.Top, _
      Width:=dWidth, Height:=dHeight)
    ch.Chart.Paste
    rng.Activate
    ch.Chart.Export sFile
    ch.Delete
    Set cmt = rng.AddComment
    cmt.Text Text:=""
    With cmt.Shape
      .Fill.UserPicture sFile
      .Width = dWidth
      .Height = dHeight
    End With
    
    ws.Protect ("123"), DrawingObjects:=False, Contents:=True, Scenarios:= _
    False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _
    True, AllowSorting:=True, AllowFiltering:=True
    
End Sub

 

[shg]

Maybe ...

    With cmt.Shape
        .Fill.UserPicture sFile
[COLOR=#ff0000]        Kill sFile[/COLOR]
        .Width = dWidth
        .Height = dHeight
    End With

 

[kstrick9]

@ shg...worked perfectly. Thanks! Using the Kill command eliminated the need for the input box which I deleted.

If anyone else needs this modified macro from another site, here it is:

Option Explicit
Sub PictureIntoComment()
'Inserts picture into cell comment. Click active cell, then click picture, run macro.
Dim ch As ChartObject
Dim dWidth As Double
Dim dHeight As Double
Dim ws As Worksheet
Dim sName As String
Dim cmt As Comment
Dim sPath As String
Dim sFile As String
Dim rng As Range
Set ws = ActiveSheet
Set rng = ActiveCell
sPath = ThisWorkbook.Path & "\"
sName = ""
If sName = "" Then sName = "Picture_" & Format(Date, "ddmmyy")
sFile = sPath & sName & ".jpg"
    dWidth = Selection.Width
    dHeight = Selection.Height
    
    ws.Unprotect "123"
    
    Selection.Cut
    Set ch = ws.ChartObjects.Add(Left:=rng.Left, Top:=rng.Top, _
      Width:=dWidth, Height:=dHeight)
    ch.Chart.Paste
    rng.Activate
    ch.Chart.Export sFile
    ch.Delete
    Set cmt = rng.AddComment
    cmt.Text Text:=""
    With cmt.Shape
      .Fill.UserPicture sFile
      Kill sFile
      .Width = dWidth
      .Height = dHeight
    End With
    
    ws.Protect ("123"), DrawingObjects:=False, Contents:=True, Scenarios:= _
    False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _
    True, AllowSorting:=True, AllowFiltering:=True
    
End Sub

 

[shg]

You're welocme, good luck.

 

[Rick Rothstein]

@ shg...worked perfectly. Thanks! Using the Kill command eliminated the need for the input box which I deleted.

In case it might matter to you, I just want to point out that VBA's Kill Statement permanently deletes the file passed into it... it does not go into the Recycle Bin. If you want the deleted file to be available to be recovered by the user at a later date, that will require some code and a call to Windows API core functions.

Private Type SHFILEOPSTRUCT
  hwnd As Long
  wFunc As Long
  sFrom As String
  sTo As String
  fFlags As Integer
  fAnyOperationsAborted As Boolean
  hNameMappings As Long
  lpszProgressTitle As String
End Type

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

Private Const FO_DELETE = &H3
Private Const FOF_SILENT = &H4
Private Const FOF_NOCONFIRMATION = &H10
Private Const FOF_ALLOWUNDO = &H40

'  THIS IS THE SUBROUTINE THAT YOU CALL
Sub DeleteToRecycle(FilePathToDelete As String)
  Dim oFilAPI As SHFILEOPSTRUCT
  Dim lReturn As Long
  With oFilAPI
    .wFunc = FO_DELETE
    .sFrom = FilePathToDelete
    .sTo = vbNullChar
    .fFlags = FOF_SILENT + FOF_NOCONFIRMATION + FOF_ALLOWUNDO
  End With
  lReturn = SHFileOperation(oFilAPI)
  Exit Sub
Err_Delete:
  MsgBox (Err.Number & " - " & Err.Description)
  Err.Clear
  Resume Next
End Sub

Place all of the code above into the same general Module. The above is not a macro (although you install it to the same kind of Module as you do a macro); rather, it is a subroutine that needs to be called from other VBA code. To use the above code (to delete a file to the Recycle Bin), simply pass the file name along with its full path to the DeleteToRecycle subroutine like this...

DeleteToRecycle "C:\TEMP\TestFileNameToBeDeleted.txt"