PDA

View Full Version : Solved: Deleting file at end of code



feathers212
04-17-2007, 06:24 AM
I have the following file created by a press operator:
5520

The coding in the "Pull Ticket" sheet is leftover from when the file was created/submited. The file (Job 123456 - Run Cover - Press 1010 - 4.17.2007.xls) is saved on our network in the following location:
V:\press\Forum\Count Control\Pull Tickets\Verification Needed

A supervisor will open this file and verify that all of the counts are correct. The supervisor will then click on the "Submit Supervisor Verification" button and select his name. On clicking OK, just the range of cells containing the ticket information is saved as a picture image in a newly created workbook (Job 123456 - Run Cover - Press 1010 - 4.17.2007 Verified.xls) in another network location:
V:\press\Forum\Count Control\Pull Tickets\Counts Verified
The new workbook is then emailed out.

Up to this point everything is working fine. However, now I want to delete the original file from the "Verification Needed" folder so that the verification process is shown as complete. Please help!!

Please note, I have built everything in Excel 2003, but the primary users of this program will be using Excel 97.

Bob Phillips
04-17-2007, 07:51 AM
Just use Kill with the original file path.

feathers212
04-17-2007, 07:56 AM
Just use Kill with the original file path.

I do.

Early on in the code I define:
OriginalFileName = ActiveWorkbook.Name
OriginalFile = ActiveWorkbook.FullName
At the end I use:
Workbooks(OriginalFileName).Close SaveChanges:=False
Kill OriginalFile

feathers212
04-17-2007, 08:13 AM
Can a file kill itself? I'm wondering if that is the problem.

feathers212
04-17-2007, 08:26 AM
Answered my own question. You can't kill ThisWorkbook, but you can program it to "commit suicide":
http://puremis.net/excel/code/029.shtml

So here's what I used at the end of the code (in place of the last two lines I mentioned above):
'Close original without changes and then delete from folder
Workbooks(OriginalFileName).Save
Workbooks(OriginalFileName).ChangeFileAccess Mode:=xlReadOnly
Kill OriginalFile
Workbooks(OriginalFileName).Close SaveChanges:=False

Bob Phillips
04-17-2007, 09:36 AM
I wouldn't call that suicide, you just removed the lock that Excel has on the file, so it can still be murdered like any other.

feathers212
04-17-2007, 09:53 AM
Murdered....suicide....either way, it's gone! It did exactly what I needed it to do. But I understand what you are saying.....I understand the control that Excel has a bit more.