PDA

View Full Version : VBA to Self Destruct File



binar
09-06-2013, 07:42 PM
Fellow Forum Members,
For more times than I care to remember I have had to email Excel or WORD files as attachments that contain sensitive data wishing there was some way such files could self destruct after a single viewing.

Is it possible for a Visual Basic script to only permit viewing of information within the WORD or EXCEL file if the PC system date is before a set date within the VBA script? Or another option, can a VBA be coded to delete data in a file using date criteria? After all, what is important for me is to have the data itself deleted within the file. I don't care about leaving behind an empty WORD or EXCEL file for the person that got the file. But perhaps the most awesome option would be if the password security that is part of all Microsoft Office 2007 products can be modified with a VBA script that would make work for only a single file opening.

I have researched Information Rights Management (IRM) in the 2007 Microsoft Office suite and I think it falls short. Below is a link that provides an overview of what IRM can do and what it can not do:


http://office.microsoft.com/en-us/excel-help/information-rights-management-in-the-2007-microsoft-office-system-HA010102918.aspx


I have also looked at Digital Rights Management (DRM) options for PDFs which is outrageously expensive. Any info or code sharing will be greatly appreciated. Thanks in advance.

shrivallabha
09-06-2013, 10:44 PM
Have never had to deal with this personally so please check the following codes.

Workbook suicide:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=540
Trial period:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=475

Teeroy
09-07-2013, 06:10 AM
Nice idea shrivallabha, especially the KillMe routine (Nasty! :devil2:). You'll have to be VERY careful testing the spreadsheet containing KillMe.

I'd also suggest calling KillMe from the Workbook_BeforeClose event and incorporate the Chip Person code (http://www.cpearson.com/excel/EnableMacros.aspx (http://www.cpearson.com/excel/EnableMacros.aspx)) to ensure that macros are enabled before the user can see anything.

binar
09-07-2013, 09:17 AM
Have never had to deal with this personally so please check the following codes.

Workbook suicide:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=540
Trial period:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=475


Shrivallabha,
Thank you very much for the links. Just some questions for you or anyone out there following this thread.

Can the KillMe VBA script in the link above be crossed over to Microsoft Word and PowerPoint with minimal recoding? What part of it needs to be tweaked to use in WORD and PowerPoint?

From the two options shown in the links above, the one I think best suits my needs is the KillMe opiton. The other option I don't like because it generates a notification that the file will soon self destruct. I prefer to provide no notification at all.

Teeroy, if I understand you correctly you are advising I combine the code below:


Option Explicit
Sub KillMe()
With ThisWorkbook
.Saved = True
.ChangeFileAccess Mode:=xlReadOnly
Kill .FullName
.Close False
End With
End Sub


With which of the additional code shown in the link below?

http://www.cpearson.com/excel/workbooktimebomb.aspx


My goal is to make all of the data dissappear within the file once the expire date is reached. I'm not clear what CPearson code needs to be added to the KillMe code to make that happen. Any advice in merging the two code sets together will be greatly appreciated. Thanks.

Teeroy
09-07-2013, 03:47 PM
binar,

The Chip Pearson routines I mentioned don't need to be combined with KillMe, they just need to be present in the same workbook. If macros are not enabled on the project then VBA can't run and so the file cannot be restricted to a "one-shot" read. If you want an expiry date then you'll need to incorporate the other kb article that shrivallabha suggested to create a log file to baseline the start date or alternatively hard-code the expiry date into the sub. In the other page you looked at on Chip's site the "TimeBomb With Suicide" is close to the KillMe routine from the kb and his discussion at the top explains why you need macros enabled.

Just to be clear, KillMe will delete the file, not just clear it. The only scenario where I'm unsure of the outcome is if the file is opened from an email attachment. My best guess is you will kill the temporary file but the original attached to the email won't be affected.

SamT
09-07-2013, 04:29 PM
Use the CustomDocumentProperties or the BuiltInDocumentProperties to set or find a date/time you can check against.

Aussiebear
09-07-2013, 10:32 PM
What stops someone from stripping the data from the file by using a program such as Snagit? I fear that once a file leaves your personal control then you may as well consider the file as publicly available. it's just a matter of time.

Teeroy
09-07-2013, 11:34 PM
Hi Aussiebear, as we all know excel is simply not that secure. The procedures given will prevent a normal user, maybe even a power user, from accessing the data but anyone knowledgeable and determined can crack it (but only if they know in advance, otherwise the workbook may self-destruct before they cotton on :)).

snb
09-08-2013, 03:20 AM
@Teeroy

So what's the purpose of distributing it ?

Teeroy
09-08-2013, 03:39 AM
Hi snb,

Sometimes you may wish to grant limited access only by time or use as a one-shot. I had an occasion where a solver was perfect for a friend who happened to work for a competitor. I was happy to use it to solve his problem for him but I wouldn't have sent out a workbook that had hundreds of man-hours of research, development and IP in it.

There are always times you want to protect some information, for example I've got a couple of files with a fail-safe keyed to a domain. If no-one tries to take them outside the network they won't even know there's a lockout buit-in (and if I get questions about it failing, I'll be asking questions :) ).

binar
09-08-2013, 10:49 AM
Thanks to everyone for their post.

Teeroy, makes two excellent points:

If the reciever of the KillMe file don't know in advance the file is going to self destruct then in my opinion I think the protection is pretty damn good.
I agree that sometimes there are times when the man hours of work it took to create the data contained in a file is sometimes so great that it does indeed become valuable to the competition. In these type of cases it's smart to use something like a Killme script.


On another note. Is it possible to adopt the Killme script to a PowerPoint slideshow? Would it be as easy as changing the "With ThisWorkbook" row over to "With ThisSlideShow" as shown in the third row of the code below? Any opinions welcomed. Thanks in advance.



Option Explicit
Sub KillMe()
With ThisSlideShow
.Saved = True
.ChangeFileAccess Mode:=xlReadOnly
Kill .FullName
.Close False
End With
End Sub

Aussiebear
09-09-2013, 12:20 AM
Its a wonderful concept but you only get one chance at the file. Any User to whom you send the file will want to know why they cannot access the file on a second time, hence the message will get out. Next time they will try to find a way to overcome your protection.

snb
09-09-2013, 01:15 AM
@Teeroy,

Every time I open a file that contains a macro I will get a warning.
If I do not enable macros, no 'killing' will take place. So what's the point ?

Teeroy
09-09-2013, 01:29 AM
@Teeroy,

Every time I open a file that contains a macro I will get a warning.
If I do not enable macros, no 'killing' will take place. So what's the point ?

@snb

The Chip Pearson EnableMacros code forces the user to have macros enabled. Without referencing it I think that it works by having the data sheets set as xlveryhidden and displaying a page indicating that macros must be enabled. Having macros enabled then lets the data show and primes the (semi) security features.

snb
09-09-2013, 01:47 AM
You can't 'force' users to enable macros.

If I do not enable macros I can easily remove any autorun macro (including the 'killing' one).
After having done that I can save the file and reopen it, macros enabled but without any 'killing' device. I will also be able to make the 'very hidden' sheets visible.

Jan Karel Pieterse
09-09-2013, 02:02 AM
I agree with snb, Your protection stands or falls with the user enabling macro's. Rest assured that 95% of all users won't have a clue how to circumvent your protection. On the other hand, it is probably the other 5% you would need to worry about, as those are likely also the types of people who would be interesting in cracking the file to begin with.

Teeroy
09-09-2013, 05:23 AM
@snb and @Jan Karel Pieterse,

I don't disagree. As I mentioned in an earlier post these methods will prevent a normal user, maybe even a power user, from accessing the data but anyone knowledgeable and determined can crack it (probably 95% of this forum's members but less than 5% of the total user base). Excel is simply not that secure.

In essence the best that can be done to secure code is to create an add-in or compile a DLL; to secure data, you can't (you can only make it difficult for the majority of users). In general whatever one person can do, another can reverse-engineer.