I am looking for a VBA solution for doing simultaneous task such as rename active file, choose folder where to save the renamed file and delete old version of file. "Save as" dialogue allow the first two part of this requirement but does not delete the ex. file.
A good solution of this issue is posted by Oliver Salzburg who is also member of this forum and posted here. However that code works for Microsoft Word only I tried for Microsoft excel with little modification (ActiveDocument to ActiveWorkbook) aimed for excel but still does not work in Excel. The Oliver Salzburg VBA code with minor modification as follows here
Sub MacroXL()
' Store original name
Dim sOriginalName As String
sOriginalName = ActiveWorkbook.FullName
' Save As
Dim sFilename As String, fDialog As FileDialog, ret As Long
Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
ret = fDialog.Show
If ret <> 0 Then
sFilename = fDialog.SelectedItems(1)
Else
Exit Sub
End If
Set fDialog = Nothing
' Don't replace the original file
If sFilename = sOriginalName Then Exit Sub
ActiveWorkbook.SaveAs2 Filename:=sFilename, FileFormat:= _
wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False, CompatibilityMode:=14
' Delete original
Kill sOriginalName
End Sub
While debugging error shows as attached:
In this location:
ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:= _
wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False, CompatibilityMode:=14
Hope @Oliver Salzburg or someone with VBA skill would be able fix the above minor problem. Thank in advance.