Locking files to the office

misterjeeves

New member
Joined
Jun 4, 2013
Messages
3
Reaction score
0
Points
0
I am by no means an expert at programming excel or macros, but I have tooled my way through some issues that I was able to solve with some creative macros. However, my problem now is that I have found out that the file i have created have been leaked to other companies in my industry and they are using them without permission. I am wondering if there is a way to lock an excel file to only open if it is connected to my work network. I have figured out a way to use the Auto_Open macro with some help from Ken's post of FileFolderExist (Kudos to Ken) to verify the existence of a file on the network before opening, and at the same point, it will close excel if it does not find that file. The problem I am running into is the work-around by pressing Shift while opening the file through the excel program, thus bypassing my verification process, and allowing them to view/etc the excel file that I have spent countless hours creating and updating. I would just put a password on the file to open, but apparently the previous password had been leaked as well, so I am looking for something that I can control. Is there a feature that I am missing that would be able to accomplish this? BTW, this would have to be compatible with Excel 2002 and up, since some of my computers are running that version still.

I appreciate any help or input on this.

Thank you,
John
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Excel is not secure. Passwords can be cracked with a very simple macro...all the user needs to do is access the VBA window of the file and cut some code in and run it. See <link removed>

And if you try to password-protect the VBA project so that they don't get access to the VBA window (and so they can't paste the code in), then they can remove the VBA password very easily just by opening the file in a hex editor such as Notepad Plus and making a quick one-second tweak. See <link removed>

So bottom line, unless you somehow encrypt your file with some kind of proper encryption tool (and I'm guessing such a tool exists) then you're out of luck.
 
Last edited by a moderator:

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
It doesn't help security when someone posts links to ways of cracking passwords!

There are methods of locking files to work on a specific computer, obfuscating the code. All help but are not 100% secure.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
With respect, I beg to differ. It absolutely does help security of information when someone points out to users that the platform itself cannot be secured. Because then at least users are under no illusions otherwise.
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
I wasn't disagreeing about the Excel, but that it doesn't help security when someone posts links to ways of cracking passwords! Most other Forums do not allow such links.

There are ways but it usually involves software such as Donex
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
With respect, I beg to differ. It absolutely does help security of information when someone points out to users that the platform itself cannot be secured. Because then at least users are under no illusions otherwise.

Hey Jeff,

I replaced your links with some non-linked text. I totally agree that users need to know, and even demonstrate removing worksheet passwords via VBA when I teach classes, but I don't really like providing the code or links to it. I think with the edited post in place though, the point has definitely been made. This should make sure that future readers will know it can be done, but will be on their own to dig up the techniques elsewhere. :)

Also, just as an FYI, that hex editor method stopped working with the release of Vista, iirc. There's something about the OS or the file version (or both) that changed the structure there. Having said that, you can still buy a cheap solution to remove VBA or Workbook Open passwords... if you trust your credit card to someone who professes to make a living by hacking.

misterjeeves said:
Is there a feature that I am missing that would be able to accomplish this?

Unfortunately the only tool I'm aware of to really "harden" the security of your solution is Visual Studio. As the gents in this thread have already pointed out, Excel is completely unsecure. With Visual Studio (coding your solution in a .NET language) you can obfuscate and compile your code in ways that make it much more difficult to steal. Having said that, where there is a will there is always a way, so even that is not 100% safe, although it's probably 99% safer than Excel.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
234
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
Also, just as an FYI, that hex editor method stopped working with the release of Vista, iirc. There's something about the OS or the file version (or both) that changed the structure there.

Nope. Still works.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Might have been the method I was using then. The article linked to had very slightly different steps. :)
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
234
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
I didn't see the link so can't comment ;)
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Explain to me why it doesn't help security of information to point out that the app isn't secure by referring to easily found resources proving that it isn't secure?

Would it have been acceptable if I had said "Do a Google search on 'break Excel password' to see what I mean"? If so, isn't the difference between that and this somewhat arbitrary?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
The difference is arbitrary, I agree. Here’s where I personally go with this stuff.

I think it’s critical that users are aware that the program is not secure. I think it’s critical that they know how easy it is for a user who is intending to hack the system to do so. I have no issues in pointing out that, with intent, time and the correct search terms a user will find detailed steps on exactly how to do it on the net, for free.

What I prefer not to do is provide them with links to it, or any aids to help them in that search. (So no, I wouldn’t provide them with the Google search terms that you did.)

Knowledge that it can be done is one thing, giving them the resources to do it is another to me. I can’t stop people from discovering it, but I don’t need to be complicit in their activity. Maybe they’d use the power for good, maybe for evil, I don’t know enough to judge them, so err on the side of caution.

Does that make sense?
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Ahh well...at least you haven't replaced my:
"Do a Google search on 'break Excel password' to see what I mean"

...with:
"Do a Google search on '< Search Phrase Removed >' to see what I mean".

;-)
 

misterjeeves

New member
Joined
Jun 4, 2013
Messages
3
Reaction score
0
Points
0
Thank you all for renewing my faith in excel on how secure (or insecure) it is. FWIW, below i am posting how i managed to hide (xlVeryHidden) all the sheets upon closing the file and saving, as well as i have it running the Auto_Open macro to verify file and unhide the sheets when opening and/or running ANY other macro in the file (just in case they manage to get it open in the first place). Note that in this scenario, "Sheet1" is completely blank, and all other sheets are used for my calculations. That way, unless they break the code to look at the file referenced in the VB code, it will be almost useless to them.

If there are any other suggestions on how i could improve upon this, let me know.

-misterjeeves

--------------------------------------

Sub Auto_Open()
If FileFolderExists("E:\Verify\ThisFileIsAPlaceHolder.txt") Then
Application.ScreenUpdating = False
Sheets("Tab1").Visible = True
Sheets("Tab2").Visible = True
Sheets("Tab3").Visible = True
Sheets("Tab1").Select
Range("a3").Select
Else
Application.Quit
End If
End Sub

---------------------------------------

Public Function FileFolderExists(strFullPath As String) As Boolean
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

EarlyExit:
On Error GoTo 0
End Function

---------------------------------------

Sub Auto_Close()
Application.ScreenUpdating = False
Sheets("Sheet1").Visible = True
Sheets("Tab1").Visible = xlVeryHidden
Sheets("Tab2").Visible = xlVeryHidden
Sheets("Tab3").Visible = xlVeryHidden
End Sub
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hey there,

You may also want to implement something like this as well: http://www.excelguru.ca/content.php?162-Force-User-To-Enable-Macros

I still won't provide perfect security, as any VBA guru can hack it easily, but your current code would allow a user to:
  • Open the workbook (unhiding the worksheets)
  • Save the workbook
  • Close it without saving changes, never triggering the hide

The macro I linked to is intended to make sure that macros are always enabled when a user opens the file.
 

misterjeeves

New member
Joined
Jun 4, 2013
Messages
3
Reaction score
0
Points
0
@ Ken-

thank you! this runs smoother and better security than my code. At least this will prevent the typical user from being able to steal and utilize my files for their own gain.

thank you again!!

-misterjeeves
 
Top