Cannot use Kill to delete file

[Julesdude]

Hi,

I've tried multiple ways to get this working, but I am running into constant errors on the Kill command.
I have an add-in where this code resides. It is trying to strip out worksheet macros from a workbook template it has opened. I would like it to save as a .xls to a restore folder in it's current form (say User's Documents folder), then save itself as a .xlsx to strip out the worksheet embedded code in the original location it was opened (say, desktop), before deleting the original .xls it was opened as (desktop)

The save routines work, but when it gets to the Kill command, either permissions are denied error comes up or bad path/file name comes up.

Here's the code in current format:

Sub routine(wb as workbook)
Dim oldFullFilePath As String ' local file path of stored documentDim oldFullName As String ' current full path of the workbook
Dim NameNoExt As String ' current filename
Dim oldFullFilePathNoExt As String ' workbook name without the .ext
Dim storedFilePath As String ' Full file path without the .ext

oldFullFilePath = wb.FullName 
oldFullName = wb.Name 
NameNoExt = Left(oldFullName, inStr(oldFullName, ".") - 1) 
oldFullFilePathNoExt = Left(oldFullFilePath, InStr(oldFullFilePath, ".") - 1) 
storedFilePath = wb.Path ' i.e C:\\User\Desktop

' save as current file as .xlsx in same current location
'wb.SaveAs currentFullFileNameNoExt, FileFormat:=51

    If Not Right$(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".")) = "xlsx" Then
    wb.SaveAs Filename:=Environ("userprofile") & "\Documents\" & oldFullName, FileFormat:=56

    ' save as .xlsx macro free workbook into same location as it was originally opened
    wb.SaveAs Filename:=oldFullFilePathNoExt, FileFormat:=51

    ' Delete the original .xls file
    Kill oldFullFilePath
   
    End If
End Sub

Where am I going wrong?

 

[mole999]

maybe this will guide you > https://www.mrexcel.com/forum/excel-questions/232591-visual-basic-applications-kill-command.html

 

[Julesdude]

Thanks mole,
It's of the same nature, i.e. Kill function, but that's about it. I'm not trying to delete a folder as such, just a single file. It won't delete the file, even though it's just on the user's desktop.

 

[Norie]

Does the file actually exist?

 

[Rick Rothstein]

storedFilePath = wb.Path ' i.e C:\\User\Desktop

If your example is written correctly, you have one too many backslashes after the colon.

 

[Julesdude]

Sorry for the delay. I've been testing some more scenarios based on your recommendations so far.
Norrie: yes the file exists - I was checking the path in the immediate window and using debug.print - was the exact same path as the original .fullname of the workbook.

I still have an issue but have become a bit clearer about why this might not be working. Just the background again:
- I am trying to get an add-in that opens with Excel to ignore a workbook open event in a workbook that is opened, and to strip out ALL code residing in it
- The add-in has a class created mapped to workbook and worksheet events that fire off when this particular workbook is opened. These all work fine.
- I need the add-in to take precedence - not to allow the workbook open event to fire when the template is opened
- The add-in eventually saves the code embedded template as an .xlsx (code above) which SHOULD strip the code out. However IT DOES NOT.

I think this may be because the workbook open code is running, even though I have put an application.enableevents = False line just before the workbook is opened.

I thought add-ins ran before everything else and so could control and cancel code running in an opening workbook's workbook - open routine?

Is there any other way from the add-in I can halt that code in the template from running before it executes?

 

[shg]

Is there any other way from the add-in I can halt that code in the template from running before it executes?

See the Application.AutomationSecurity property.

 

[Julesdude]

Thanks shg,
I've looked into that one a bit more now. But I don't think it would work. Looks like I'd need to use something like:

Application.AutomationSecurity = msoAutomationSecurityForceDisable

Looks like this would work only when placed before an open workbook command. That wouldn't work though. Let's take an example:

- a user opens a workbook with the workbook code residing in it (Excel is not currently open)
- Excel opens first and loads add-ins first
- Add-in has a workbook open routine in it that starts straight away
- Then the .xls file the user has opened launches its open routine
- both of the routines run, one after the other, which creates a duplication.

I'm not sure where I would place the command above then, and even whether it can actually halt the .xls workbook's code from executing?
Would you have any suggestions?

 

[shg]

You need to open Excel with the add-in first, then open other files.