Perform SaveAs and Delete Original Copy

[YellowLabPro]

I have three steps here, I have solved for the first two. I need to solve for the last step, deleting the original file which still resides on the Desktop.

The original file name resides initially on the desktop.
"File123abc.xls"

I need to save the original file name to directory location 1.
"File123abc.xls" gets saved to folder C:\Folder1\folder2\

Next- save the same file w/ a new name to the Desktop.
"File123abc.xls" gets saved as "File123.xls" to C:\Desktop\

Last- Delete the original file on the desktop.
"File123abc.xls" on C:\Desktop is deleted, leaving the new file "File123.xls" as the new file.

My code below handles all but the last condition: there are a couple of commented out lines, as I was experimenting w/ some different techniques.

[VBA]
Option Explicit

Sub TGSUpdater()
Dim WBs As Workbook, WBt As Workbook, WBvar As Workbook
Dim iStatus As Long
Dim sPathSave As String, sPath As String, sPathSaveDT As String, sWBt As String
sPathSave = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\Processed Files\"
sPath = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\"
sPathSaveDT = "C:\Documents and Settings\Doug\Desktop\"
Err.Clear
On Error Resume Next
Set WBs = ActiveWorkbook
Set WBt = Workbooks("TGSUpdater.xls")
sWBt = "TGSUpdater.xls"
Application.DisplayAlerts = False
WBs.SaveAs Filename:=sPathSave & WBs.Name
'Set WBvar = ActiveWorkbook
'Name "spathsavedt ActiveWorkbook" As "sPathSaveDT TGSItemRecordCreatorMaster.xls"
WBs.SaveAs Filename:=sPathSaveDT & "TGSItemRecordCreatorMaster.xls"
'WBvar.Delete
iStatus = Err
If Err <> 0 Then
On Error GoTo 0
Workbooks.Open sPath & sWBt
End If
Application.Run "TGSUpdater.xls!UpdateRecords"
WBs.Save
End Sub
[/VBA]

[mdmackillop]

A simplified example.
[VBA]Sub SaveAndDelete()
Dim j As String, k As String
j = ThisWorkbook.FullName
k = ThisWorkbook.Name
ThisWorkbook.SaveCopyAs "C:\Test\" & k
ThisWorkbook.SaveAs "C:\book2.xls"
Kill j
End Sub

[/VBA]

[Bob Phillips]

No need to delete anything

[vba]

Sub SaveAndDelete()
Const RenameAs As String = "C:\Desktop\File123.xls"
Const SaveAsPath As String = "C:\Folder1\folder2\"

ThisWorkbook.SaveAs SaveAsPath & ThisWorkbook.Name
Name ThisWorkbook.FullName As RenameAs

End Sub
[/vba]

[YellowLabPro]

I have my rough copy attempt cobbled out here, and it works-- . This uses Md's approach. Bob, I am out for awhile today, but will have a crack at your strategy. I had tried this earlier, prior to you suggesting it, but was it unsuccessful. Taking a cursury look at it, I think you have more fiber to it.

If either of you would check my error method to see if there might be an issue- I am using a GoTo, and see that others frown on this method, but cannot figure out any other way to handle this.

What it is doing is seeing if the base file name is the active file name, if it is then it skips deleting and continues w/ the code.

[vba]
Sub TGSUpdater()
Dim WBt As Workbook
Dim WBs As String, WBtgs As String
Dim iStatus As Long
Dim sPathSave As String, sPath As String, sPathSaveDT As String, sWBt As String
'Stop
sPathSave = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\Processed Files\"
sPath = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\"
sPathSaveDT = "C:\Documents and Settings\Doug\Desktop\"


WBs = ThisWorkbook.Name
WBtgs = "TGSItemRecordCreatorMaster.xls"
sWBt = "TGSUpdater.xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=sPathSave & WBs
ThisWorkbook.SaveAs Filename:=sPathSaveDT & WBtgs
TGSItemRecordCreatorMaster.xls"

If WBs = WBtgs Then
GoTo Continue:
Else
Kill sPathSaveDT & WBs
End If
Continue: Workbooks.Open sPath & sWBt
Application.Run "TGSUpdater.xls!UpdateRecords"

Set WBt = Workbooks("TGSUpdater.xls")
WBt.Save
Application.DisplayAlerts = True
End Sub
[/vba]

[Bob Phillips]

[vba]

Sub TGSUpdater()
Dim WBt As Workbook
Dim WBs As String, WBtgs As String
Dim iStatus As Long
Dim sPathSave As String, sPath As String, sPathSaveDT As String, sWBt As String
'Stop
sPathSave = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\Processed Files\"
sPath = "C:\Documents and Settings\Doug\Desktop\TGS\TGSFiles\"
sPathSaveDT = "C:\Documents and Settings\Doug\Desktop\"


WBs = ThisWorkbook.Name
WBtgs = "TGSItemRecordCreatorMaster.xls"
sWBt = "TGSUpdater.xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=sPathSave & WBs
ThisWorkbook.SaveAs Filename:=sPathSaveDT & WBtgs
TGSItemRecordCreatorMaster.xls " "

If Not WBs = WBtgs Then

Kill sPathSaveDT & WBs
Else

Workbooks.Open sPath & sWBt
Application.Run "TGSUpdater.xls!UpdateRecords"

Set WBt = Workbooks("TGSUpdater.xls")
WBt.Save
End If

Application.DisplayAlerts = True
End Sub
[/vba]

[YellowLabPro]

Croggy- Thanks Bob.
The one thing that troubles me about that piece of code, mine not yours, is that my code seems to compare the workbooks, not the names, which would seem to be a more accurate way to do this.
But placing the .Name after the variable workbook name is not legal.
WBs.Name = WBtgs.Name is no good. Your opinion on this please.

[Bob Phillips]

You can't add a Name property because you have already extracted the Name of the workbooks and loaded them into strings.It is the strings that you are comparing.

[YellowLabPro]

ok, good.... thank you