vba to kill workbook at open

[cwchan220]

Hi,

I want to expire a workbook on a specified date if the user opens it. I was trying to use the Workbook_open event but I am running into different errors. This file is in a shared drive and it is password protected. Could those be the reason for the permission denied error? I also got the "runtime error 91: object variable or with block variable not set" with the below code I was testing with.

dt = Format(Now + 2, "mmddyy")

On Error GoTo ErrorHandler
Select Case dt
Case Is > "101614"

With ActiveWorkbook
If .Path <> "" Then
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
MsgBox "File expired"
End If
End With
Exit Sub

ErrorHandler:
MsgBox "Fail to delete file: " & ActiveWorkbook.FullName
End Select


End Sub

 

[Andrew Poulsom]

Here you are:

Timebombing A Workbook

 

[cwchan220]

Thanks Andrew.

I tried using one of the codes in the link and I get an error: "file not found".Only difference with the link is I put it in Workbook_Open instead of a regular module. Looks like it converts it to a Read Only file before running into the error. Any idea how to fix this?

[/COLOR]Private Sub Workbook_Open()

With ThisWorkbook
        Application.DisplayAlerts = False
        If .Path <> vbNullString Then
            .ChangeFileAccess xlReadOnly
            Kill .FullName
        End If
        ThisWorkbook.Close SaveChanges:=False
    End With


End Sub


[COLOR=#333333]

 

[cwchan220]

I got the same error when I tried a different code provided in an old post: http://www.mrexcel.com/forum/excel-...-visual-basic-applications-2.html#post3969549. This led me to think the issue might be that I am trying to execute the code on a protected file. Is there a way around this? Thanks!

 

[Andrew Poulsom]

What do you mean by "a protected file" exactly?

 

[cwchan220]

It's protected view and it says the file came from the internet even though it's from the shared drive. I've played with the protected view setting but I don't think I want to disable the protected view for files originating from the internet. Maybe my assumption for the "file not found" error is incorrect. Is there any reason the code isn't running?

 

[Andrew Poulsom]

Which line causes the error? Can you give an example of the file's path?

 

[jtakw]

It's protected view and it says the file came from the internet even though it's from the shared drive. I've played with the protected view setting but I don't think I want to disable the protected view for files originating from the internet. Maybe my assumption for the "file not found" error is incorrect. Is there any reason the code isn't running?

You don't need to change/disable the protected view settings, when you open your file, the protected view warning will alert you to "enable editing", click on "enable editing", until you do so, your codes won't run properly because editing is disabled. If you don't "enable editing", you can view the file but can NOT make changes to it.

 

[cwchan220]

I want to run the kill code when the workbook opens so I need something to override enable editing alert if that is the reason I am getting the error.

 

[jtakw]

I want to run the kill code when the workbook opens so I need something to override enable editing alert if that is the reason I am getting the error.

You can not overide it. Make a copy of your file in a safe location, enable editing, then run your kill code to see if that's the problem.