YellowLabPro
08-18-2007, 09:10 PM
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.
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
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.
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