Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
Have a file I call Updater. Sent out with File F2
Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
On Error GoTo ErrorHandler
With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName Exit Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Have a file I call Updater. Sent out with File F2 Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
Bob,
As a friendly suggestion, the error handler message box mentions "delete"ing the file. I don't believe the code you posted actually "deletes" the file, instead, it just unloads it, kills it, etc. If the file were "deleted" I would not be able to open/run the file at a later date. Unloading/Killing the file simply means I would need to load it from storage(disk) again at my convienience. Sincerely, Christmas May "Bob Phillips" wrote: On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName Exit Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Have a file I call Updater. Sent out with File F2 Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
Thanks for the input.
Yes, I want the file GONE. They would have to reload it from their CD in order to use it a 2nd time. 1: The following layout gives me a 'compile error - Expect end of sub. 2: If I could trouble you to check the rest over. I'm having difficulty in two locations, as marked <<<=== I feel I've made a real mess of this one, although it seems to (almost) work. Sub QCNum_Updater() ' QCNum_Updater Macro Dim myQCNum As Workbook Dim myQCNum_1 As Workbook Dim myQCNum_OLD As Workbook Dim myQCNum_Updater As Workbook Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False Call GetInstructions Set myQCNum = Workbooks.Open("C:\Excel Add_Ins\QCNum.xls") With myQCNum .SaveAs (["QCNum_OLD"]) .Close SaveChanges:=True End With Set myQCNum = Workbooks.Open("C:\Excel Add_Ins\QCNum.xls") Set myQCNum_1 = Workbooks.Open("C:\Excel Add_Ins\QCNum_1.xls") ' TO (QCNum_1), then (QCNum) FROM: Sheet 1, D6 = Salesmans Code Number ' Sheet 2, G3 = Current Contract Number myQCNum_1.Worksheets("Sheet1").Range("D6").Value = _ myQCNum.Worksheets("Sheet1").Range("D6").Value myQCNum_1.Worksheets("Sheet2").Range("G3").Value = _ myQCNum.Worksheets("Sheet2").Range("G3").Value With myQCNum_1 .SaveCopyAs (["C:\Excel Add_Ins\QCNum.xls"]) '<<=Does not want to work .Close SaveChanges:=True End With 'Delete Existing QCNum_1 File ' Kill "C:\Excel Add_Ins\QCNum_1.xls" '<<<==== Works Great!! Application.EnableEvents = True Application.ScreenUpdating = True Application.DisplayAlerts = True Call Complete ' On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub 'ErrorHandler: MsgBox "Fail to delete File:" & ActiveWorkbook.FullName Application.EnableEvents = True Application.ScreenUpdating = True Application.DisplayAlerts = True 'Exit Sub "Christmas May" wrote: Bob, As a friendly suggestion, the error handler message box mentions "delete"ing the file. I don't believe the code you posted actually "deletes" the file, instead, it just unloads it, kills it, etc. If the file were "deleted" I would not be able to open/run the file at a later date. Unloading/Killing the file simply means I would need to load it from storage(disk) again at my convienience. Sincerely, Christmas May "Bob Phillips" wrote: On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName Exit Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Have a file I call Updater. Sent out with File F2 Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
I tried Bob's code (curiosity) and it deleted the file (activeworkbook) from
the hard drive. After I closed the activebook, there was nothing to reload. It looks to me that if a line is added to the macro to close the book w/o saving it, it's history. Did you try the code? "Christmas May" wrote: Bob, As a friendly suggestion, the error handler message box mentions "delete"ing the file. I don't believe the code you posted actually "deletes" the file, instead, it just unloads it, kills it, etc. If the file were "deleted" I would not be able to open/run the file at a later date. Unloading/Killing the file simply means I would need to load it from storage(disk) again at my convienience. Sincerely, Christmas May "Bob Phillips" wrote: On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName Exit Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Have a file I call Updater. Sent out with File F2 Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
Kill is delete.
Try it, you'll see. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Christmas May" wrote in message ... Bob, As a friendly suggestion, the error handler message box mentions "delete"ing the file. I don't believe the code you posted actually "deletes" the file, instead, it just unloads it, kills it, etc. If the file were "deleted" I would not be able to open/run the file at a later date. Unloading/Killing the file simply means I would need to load it from storage(disk) again at my convienience. Sincerely, Christmas May "Bob Phillips" wrote: On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName Exit Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Have a file I call Updater. Sent out with File F2 Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
Thanks for the input so far.
My level of competance is so low that I would not dare to question a suggestion without trying it out. My first problem is that whenever I try to run the code (added in with my code), the cursor jumps to the very end and comes up with 'Compile Error - Expect End Sub' If I change the last Exit to End, it still comes up with the same message. If someone could point me in the right direction here, then I can do further testing and see what else I have messed up. Thank-you NOTE: I Rem'd out the one Kill Statement (for now) because it DOES work, and I got tired of having to reinstall that file every time I did a test. "Bob Phillips" wrote: Kill is delete. Try it, you'll see. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Christmas May" wrote in message ... Bob, As a friendly suggestion, the error handler message box mentions "delete"ing the file. I don't believe the code you posted actually "deletes" the file, instead, it just unloads it, kills it, etc. If the file were "deleted" I would not be able to open/run the file at a later date. Unloading/Killing the file simply means I would need to load it from storage(disk) again at my convienience. Sincerely, Christmas May "Bob Phillips" wrote: On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName Exit Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Have a file I call Updater. Sent out with File F2 Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete (kill) active workbook on close: Possible?
I just gave the code not a macro.
Us this and run it from Excel (ToolsMacroMacros... select KillMyFile from the list and run it) Sub KillMyFile() On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Thanks for the input so far. My level of competance is so low that I would not dare to question a suggestion without trying it out. My first problem is that whenever I try to run the code (added in with my code), the cursor jumps to the very end and comes up with 'Compile Error - Expect End Sub' If I change the last Exit to End, it still comes up with the same message. If someone could point me in the right direction here, then I can do further testing and see what else I have messed up. Thank-you NOTE: I Rem'd out the one Kill Statement (for now) because it DOES work, and I got tired of having to reinstall that file every time I did a test. "Bob Phillips" wrote: Kill is delete. Try it, you'll see. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Christmas May" wrote in message ... Bob, As a friendly suggestion, the error handler message box mentions "delete"ing the file. I don't believe the code you posted actually "deletes" the file, instead, it just unloads it, kills it, etc. If the file were "deleted" I would not be able to open/run the file at a later date. Unloading/Killing the file simply means I would need to load it from storage(disk) again at my convienience. Sincerely, Christmas May "Bob Phillips" wrote: On Error GoTo ErrorHandler With ActiveWorkbook If .Path < "" Then .Saved = True .ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName End If End With Exit Sub ErrorHandler: MsgBox "Fail to delete file: " & ActiveWorkbook.FullName Exit Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BEEJAY" wrote in message ... Have a file I call Updater. Sent out with File F2 Updater takes info from F1, copies to F2. When process complete, I'd like Updater to be killed, but it is the active workbook. Is there a way to kill a file on closing? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to close specific workbook not all active workbooks? | Excel Programming | |||
keystroke to close active workbook window | Excel Worksheet Functions | |||
Delete (Kill) Active Workbook | Excel Programming | |||
Excel shoud not close all active books when clicking close button | Excel Discussion (Misc queries) | |||
delete toolbar on workbook close | Excel Programming |