+ Reply to Thread
Results 1 to 8 of 8

Delete (kill) active workbook on close: Possible?

  1. #1
    BEEJAY
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Christmas May
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #4
    BEEJAY
    Guest

    Re: 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #5
    JMB
    Guest

    Re: 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #6
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #7
    BEEJAY
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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. #8
    Bob Phillips
    Guest

    Re: Delete (kill) active workbook on close: Possible?

    I just gave the code not a macro.

    Us this and run it from Excel (Tools>Macro>Macros... 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in

    message
    > > news:[email protected]...
    > > > 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1