To register for an Internet.com membership to receive newsletters and white papers, use the Register button ABOVE.
To participate in the message forums BELOW, click here
VBForums  

VB Wire News
Part 10 of the Visual Basic .NET 2010 Express Tutorial Complete!
How to Use the Visual Studio Code Analysis Tool FxCop
Article :: Interview with Andrei Alexandrescu (Part 3 of 3)
Introducing Visual Studio LightSwitch
Visual Studio LightSwitch Beta 1 is Available



Go Back   VBForums > Visual Basic > Office Development

Reply Post New Thread
 
Thread Tools Display Modes
Old May 11th, 2007, 03:04 PM   #1
mariocatch
New Member
 
Join Date: Apr 07
Posts: 9
mariocatch is an unknown quantity at this point (<10)
[2003] Excel+VBA: Save old doc to new location/delete previous doc

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 is offline   Reply With Quote
Old May 11th, 2007, 11:38 PM   #2
mariocatch
New Member
 
Join Date: Apr 07
Posts: 9
mariocatch is an unknown quantity at this point (<10)
Re: [2003] Excel+VBA: Save old doc to new location/delete previous doc

resolved
mariocatch is offline   Reply With Quote
Old May 11th, 2007, 11:45 PM   #3
westconn1
PowerPoster
 
Join Date: Dec 04
Posts: 13,424
westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)
Re: [2003] Excel+VBA: Save old doc to new location/delete previous doc

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

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

vb Code:
  1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  2. If Not IsEmpty(Sheets("sheet1").Range("x1")) Then
  3.     If Not Dir(Sheets("sheet1").Range("x1")) = "" Then
  4.         Kill Sheets("sheet1").Range("x1")
  5.         Sheets("sheet1").Range("x1") = ""
  6.     End If
  7. End If
  8. 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
__________________
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete
westconn1 is online now   Reply With Quote
Old May 11th, 2007, 11:52 PM   #4
mariocatch
New Member
 
Join Date: Apr 07
Posts: 9
mariocatch is an unknown quantity at this point (<10)
Re: [2003] Excel+VBA: Save old doc to new location/delete previous doc

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
mariocatch is offline   Reply With Quote
Old May 12th, 2007, 03:26 AM   #5
westconn1
PowerPoster
 
Join Date: Dec 04
Posts: 13,424
westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)westconn1 is a splendid one to behold (700+)
Re: [2003] Excel+VBA: Save old doc to new location/delete previous doc

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
__________________
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete
westconn1 is online now   Reply With Quote
Reply

Go Back   VBForums > Visual Basic > Office Development


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 05:15 AM.





Acceptable Use Policy

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.