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:
Where am I going wrong?
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:
Code:
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?