[2003] Excel+VBA: Save old doc to new location/delete previous doc

[mariocatch]

Here's my current issue, hopefully someone has some advice on this solution:


Basically, I have an excel doc saved somewhere on a networked drive. What I want, is if someone goes to that excel doc, clicks a button, it would save that excel doc to another location (i can do this already), and delete the old excel doc from the previous location.

I'm not sure how I would go about automating the delete process of the old excel doc. I can run a batch file, but a couple of problems arise there;
1) What if there are more than one excel docs in the folder. The batch file needs to know which one to delete.
2) How can an excel doc run a batch file if its already closed.

Not sure if there are answers to these other than it can't be done.

Any help would be appreciated. Thanks.

[mariocatch]

resolved

[westconn1]

there are several ways this could be achieved

one that springs to mind is to put the filename (with path) into an empty cell prior to doing saveas

Workbooks("jw.xls").Sheets("sheet1").Range("x1") = Workbooks("jw.xls").FullName ' select an empty cell somewhere
Workbooks("jw.xls").SaveAs "jw2.xls"

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not IsEmpty(Sheets("sheet1").Range("x1")) Then
    If Not Dir(Sheets("sheet1").Range("x1")) = "" Then
        Kill Sheets("sheet1").Range("x1")
        Sheets("sheet1").Range("x1") = ""
    End If
End If
 
End Sub

note this could cause problem if later you wanted to store a filename in the selected cell, also the code will run everytime the selection changes
you could put the delete code into the workbook open event, but it will not run until the next time the file is opened, but that may not matter,
there will be other ways to do the same thing

[mariocatch]

I already resolved this, here's how I chose to do it:

1) User clicks button
2) Current xls doc is saved to a different location
3) A pop-up comes up indicating to close the current workbook.
4) Upon confirming the pop-up, a batch file is ran on a 30 second timer.
5) After 30 seconds, the old xls document is deleted automatically from the batch file.
6) Issue resolved

[westconn1]

when you saveas the original document closes without saving changes, the new (savedas) document is the one currently open, so there should be no need to close the document to delete the original