PDA

View Full Version : Excel VBA: limit file opening times, AND prevent copying. plus authorization code



DBrownBear
04-16-2017, 04:38 PM
I'm new to this site, and have only a basic knowledge of VBA for Excel.
I have a number of Engineering tools in Excel that I would like to offer on a "try before buy" basis.
kb/getarticle.php?kb_id=475 does this.

I would like also to prevent copying the file (obviously this is important for "try before buy", or otherwise a file holder could just make a bunch of copies to defeat the # openings limitation.

Even more ideal would be the option to give an "authorization code" that would release the file from limited openings after purchase.

Any thoughts on this?

Dave

Logit
04-16-2017, 06:08 PM
From what I've researched, its probably best to go commercial.

Do a search for Excel To Executable. Lots of commercial products out there for a range of prices.

DBrownBear
04-17-2017, 07:24 PM
From what I've researched, its probably best to go commercial.

Do a search for Excel To Executable. Lots of commercial products out there for a range of prices.

Thanks much! I'll explore that option.

NOTE:

I tried id=475 and had the following results:



Results in VBA error:




Run-time error '75':




Path/File access error.









Error at VBA line:





Open ObsurePath & ObscureFile For Output As #2








Tried at Flash Drive folder, C:\Dave folder.



All failed with above error.





Am I missing anything? Thoughts?


'Blessings,
Dave

Logit
04-17-2017, 07:41 PM
Your link to that file doesn't get me anywhere.

Please provide the actual URL

DBrownBear
04-18-2017, 01:58 PM
The complete URL is

vbaexpress.com/kb/getarticle.php?kb_id=475,

Logit
04-18-2017, 03:40 PM
Ok ... got the file.

Which of the 5 example workbooks are you working with ?

DBrownBear
04-23-2017, 05:06 PM
The comment that I gave was with regard to the problem with the first one: RestrictUse_1Hour.xls. I suspect the same behavior is true for the other 4 similar example files.

Logit
04-23-2017, 07:39 PM
I believe I have the code edited now so it will function as desired. The one thing I am not certain about is the TRIAL PERIOD setting created by the author of the code.

Originally it was set at one hour. When I re-opened the workbook with the correct code inserted, it immediately went into EXPIRATION mode and closed the workbook. Then it, as is the goal, refused to reopen.
The code is now set for 3 days.

My suggestion is to make a copy of this file when it is downloaded. Save it somewhere and experiment with the copy.


Now ... as programmed, this macro will create a TEXT file in the C:\ directory. It also creates a Folder. Both the folder and name are such as to hopefully confuse the user so they will
think second about deleting anything. C:\SystemFileLog\SystemFileLog.txt"

You can change those to anything you want. Keep in mind, sometimes it is a real challenge to create anything on the C Drive. That is a Microsoft invention to protect their OS.

I only edited the first macro. Have not paid any attention to the bottom macro.

Here's the code :



Option Explicit


Private Sub Workbook_Open()



'*****************************************
'SET YOUR OWN TRIAL PERIOD BELOW
'Integers (1, 2, 3,...etc) = number of days use
'1/24 = 1Hr, 1/48 = 30Mins, 1/144 = 10Mins use

Const TrialPeriod# = 3 '< 3 days


Dim sFName As String
Dim intFNumber As Integer
Dim fso As Object
Dim fldrname As String
Dim fldrpath As String
Dim StartTime As Long
Dim CurrentTime As Long

Set fso = CreateObject("scripting.filesystemobject")
fldrname = "SystemFileLog"
fldrpath = "C:\" & fldrname
If Not fso.folderexists(fldrpath) Then
fso.createfolder (fldrpath)
End If

sFName = fldrpath & "\SystemFileLog.txt"
intFNumber = FreeFile

StartTime = Format(Now, "#0.#########0")

Open sFName For Output As #intFNumber
Print #1, StartTime

Close #intFNumber


If sFName = Empty Then
StartTime = Format(Now, "#0.#########0")
Open sFName For Output As #1
Print #1, StartTime
Else
Open sFName For Input As #1
Input #1, StartTime
CurrentTime = Format(Now, "#0.#########0")
If CurrentTime < StartTime + TrialPeriod Then
Close #1
Exit Sub
Else
If [A1] <> "Expired" Then
MsgBox "Sorry, your trial period has expired - your data" & vbLf & _
"will now be extracted and saved for you..." & vbLf & _
"" & vbLf & _
"This workbook will then be made unusable."
Close #1
SaveShtsAsBook
[A1] = "Expired"
ActiveWorkbook.Save
Application.Quit
ElseIf [A1] = "Expired" Then
Close #1
Application.Quit
End If
End If
End If
Close #1
End Sub


Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xls"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Open MyFilePath & "\READ ME.log" For Output As #1
Print #1, "Thank you for trying out this product."
Print #1, "If it meets your requirements, visit"
Print #1, "http://www.***xx/***x to purchase"
Print #1, "the full (unrestricted) version..."
Close #1
End Sub

DBrownBear
04-24-2017, 07:26 AM
Thanks!

Any thoughts on how to prevent (or limit the number) copying the file when "received"?

Dave

Logit
04-24-2017, 07:41 AM
That would require a number of steps.

If you mean the data that is viewed on the worksheet:

Disabling Cut/Copy/Paste
https://discuss.analyticsvidhya.com/t/can-i-prevent-someone-from-copying-the-cell-from-worksheet-in-excel/1675/2


To prevent a user from peeking inside the VBE and seeing the macro codes you can password protect the VBE with Excel's built-in password system but that is
easily by-passed by anyone moderately knowledgeable of Excel VBA.

There are other and more secure methods of protecting the VBE which are more involved with VBA coding. Examples exist on the Net.
You can search for : vba protect hide the vbe


As a general rule, most forums (including this one) don't involve themselves with discussing in depth topics of password / password breaking. Sort of
a taboo topic. Having said that, you can find what you need online in a ton of websites.

Also you need to consider that pretty much no matter what you do to protect your project, someone has developed a method of 'breaking' that protection.
Again, if they are moderately knowledgeable of VBA and excel. Excel is not inherently designed to be a 'secure' environment.

My research has determined the most secure method is to compile your project into an .EXE (executable stand alone product) using one of a myriad of
commercial packages available. Search for Excel to EXE. In order for someone to deconstruct your product from an EXE is to use a HEX editor or similar
to convert the machine code into something recognizable. This gets down to the 'hacker level' of which I surmise most all Excel users are not.