Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook Delete After Expiration (Office XP)

    Hans and all,

    This code doesn't seem to work, Where have I done wrong?? Pls advise


    Private Sub Workbook_Open()
    MsgBox "This file will expire on 8-20-2005"
    End Sub


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Date >= #8/20/2004# Then
    ThisWorkbook.Saved = True
    MsgBox "This file has expired, it will be deleted!"
    ThisWorkbook.ChangeFileAccess xlReadOnly
    Kill ThisWorkbook.FullName
    End If
    End Sub


    Thanks, kun

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Workbook Delete After Expiration (Office XP)

    hi kun,

    This seems to work OK:

    Private Sub Workbook_Open()
    Dim KillDate
    KillDate = DateSerial(2005, 8, 20)
    If Date >= KillDate Then
    If Not ThisWorkbook.ReadOnly Then ThisWorkbook.ChangeFileAccess xlReadOnly
    MsgBox "This file has expired, it will be deleted!"
    Kill ThisWorkbook.FullName
    ThisWorkbook.Saved = True
    ThisWorkbook.Close False
    Else
    MsgBox "This file will expire on " & KillDate
    End If
    End Sub

    Cheers
    PS: your code had a different date in the msgbox to the one used to trigger the deletion.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Delete After Expiration (Office XP)

    Try changing this line:

    <code>
    If Date >= #8/20/2004# Then
    </code>

    to this:

    <code>
    If Date >= DateSerial(2005, 8, 20) Then
    </code>
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Delete After Expiration (Office XP)

    Hi Macropod and Legare,

    Thank for the fast response! Its working like a charm now.

    thanks, kun

Posting Permissions

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