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

 

[cwchan220]

Hi jtakw,

Thank you for the suggestion. When I made a copy to my desktop, the kill code worked.

Private Sub Workbook_Open()

With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With


End Sub

 

[cwchan220]

From what I gathered thus far, the original file is converted into a read-only so it doesn't run into the permission denied error for trying to kill an opened file. However, I still can't figure out why it is giving me the file not found error when the file name/path is correct. The code works if the file resides in a trusted location like my desktop but the error pops up when it is on the shared drive. Does anyone know why this is?

 

[jtakw]

Hi Chan (I'm assuming, please correct me if I'm wrong),

As stated before, your file is "read only" because you did not "enable editing" when the file was opened from the shared drive.

I have a few questions:
1. Must the file be on the shared drive? (If not, you won't have to deal with the "Protected View / Enable Editing" issue.)
2. If the file must be on the shared drive for other users, and you want to kill the file after a certain amount of time (say 30 days), why can't you just delete the file manually when the time's up?
3. Otherwise, can you make a duplicate of the file on the shared drive, open it, enable editing, and run the kill code to confirm it works. If it does, there is a sort of "work around" to accomplish what you want.

Wong

 

[cwchan220]

Hi Wong,

Correct, the file is "read only" since I didn't enable editing from the protected view but I've tried it on a regular module after enabling editing instead of Workbook_open and I get the same file not found error.

The file has to be on the shared drive for others to access so I don't have to keep sending updated versions. I don't want to keep track of files that I need to delete so I want to include this code to expire the files when the "trial period" runs out.

I also just posted this issue on kill code with file not found error

 

[jtakw]

You said the kill code works if the file is on your local drive, but not on the shared (network) drive.

Looking at your code and the problem you're having with "file not found", I'm quite certain your code is not referencing the file path correctly.
I believe the FullName method only works for files on local drives.
Look into UNC (universal naming convention) for solution. To get you started Universal network drive mapping with Excel 2010 VBA | dedicatedexcel.com

PS. When I said I was assuming, I meant your name.

 

[cwchan220]

Ah, I see...

I tried viewing the filepath with msgbox .fullname and it seemed to be correct. I will read that link and see if I can find a solution. Thanks, Wong.