+ Reply to Thread
Results 1 to 5 of 5

Delete (Kill) Active Workbook

  1. #1
    stefan via OfficeKB.com
    Guest

    Delete (Kill) Active Workbook

    Hi,
    I'd like to open an existing file, save it automatically by macro in a
    different location with a new file name. This portion i can do. I'd like then
    to delete the original, still active/open workbook. Any suggestions?
    Thank you,
    Stefan

  2. #2
    okaizawa
    Guest

    Re: Delete (Kill) Active Workbook

    Hi,

    Here is an example:

    Sub Test()
    On Error GoTo ErrorHandler
    If ActiveWorkbook.Path <> "" Then
    If Not ActiveWorkbook.ReadOnly Then
    ActiveWorkbook.Saved = True
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    End If
    Kill ActiveWorkbook.FullName
    End If
    Exit Sub

    ErrorHandler:
    MsgBox "Fail to delete file: " & ActiveWorkbook.FullName
    Exit Sub

    End Sub

    I am not sure it is perfectly all right.
    I recommend to close the workbook after deleted.

    --
    HTH

    okaizawa

    stefan via OfficeKB.com wrote:
    > Hi,
    > I'd like to open an existing file, save it automatically by macro in a
    > different location with a new file name. This portion i can do. I'd like then
    > to delete the original, still active/open workbook. Any suggestions?
    > Thank you,
    > Stefan


  3. #3
    Bob Phillips
    Guest

    Re: Delete (Kill) Active Workbook

    Why bother testing if readonly, just set it

    Sub Test()
    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

    End Sub


    --
    HTH

    Bob Phillips

    "okaizawa" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > Here is an example:
    >
    > Sub Test()
    > On Error GoTo ErrorHandler
    > If ActiveWorkbook.Path <> "" Then
    > If Not ActiveWorkbook.ReadOnly Then
    > ActiveWorkbook.Saved = True
    > ActiveWorkbook.ChangeFileAccess xlReadOnly
    > End If
    > Kill ActiveWorkbook.FullName
    > End If
    > Exit Sub
    >
    > ErrorHandler:
    > MsgBox "Fail to delete file: " & ActiveWorkbook.FullName
    > Exit Sub
    >
    > End Sub
    >
    > I am not sure it is perfectly all right.
    > I recommend to close the workbook after deleted.
    >
    > --
    > HTH
    >
    > okaizawa
    >
    > stefan via OfficeKB.com wrote:
    > > Hi,
    > > I'd like to open an existing file, save it automatically by macro in a
    > > different location with a new file name. This portion i can do. I'd like

    then
    > > to delete the original, still active/open workbook. Any suggestions?
    > > Thank you,
    > > Stefan




  4. #4
    okaizawa
    Guest

    Re: Delete (Kill) Active Workbook

    Bob,

    >Why bother testing if readonly, just set it

    That is only an example.
    If a workbook is readonly, "ChangeFileAccess xlReadOnly" fails.
    (I use Excel 2000)

    --
    Regards,
    okaizawa

  5. #5
    stefan via OfficeKB.com
    Guest

    Re: Delete (Kill) Active Workbook

    Hello Bob, Hello okaizawa,

    Thank you for your help. This works. I went without the fail-save-option as i
    am controling the workbooks that are being processed and none would be
    readonly to begin with. So it should not be an issue... if so, i'd confuse
    the user one way or another anyway. ;-)

    Thank you again.

    Stefan

    okaizawa wrote:
    >Bob,
    >
    >>Why bother testing if readonly, just set it

    >That is only an example.
    >If a workbook is readonly, "ChangeFileAccess xlReadOnly" fails.
    >(I use Excel 2000)
    >



    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...mming/200507/1

+ 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