Solved

Maintaining spreadsheet security whilst performing data connection refreshes

Posted on 2016-11-01
3
40 Views
Last Modified: 2016-11-29
When you protect a spreadsheet with "protect sheet" & "protect workbook" to effectively protect your IP. It seems that you need to lift all the protect to allow data connections to update.

Is there a recognised work around for this that would stand up to a penetration test?
All I can think of is a VBA script to perform the following

Unprotect workbook
Update all connections
Protect workbook

All the user has to do is press the escape key to interrupt the script during the data update and the security is no longer applied

I am sure there has to be a better way to update multiple data connections maintaining spreadsheet integrity
0
Comment
Question by:Chris Ogilvie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points
ID: 41869399
The only way I know of to update data connections in a protected sheet is as you've already said - unprotect, update and re-protect. To counter the use of the Escape key, you could use the Workbook_SheetSelectionChange in the ThisWorkbook module and check if workbook/worksheet is protected and if not, protect it. While the sheet may be unprotected immediately after the Escape key is pressed, as soon as they select another cell it will be protected again.

Bear in mind though that protected Worksheets and Workbooks are fairly easy to break into.
0
 
LVL 22

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 250 total points
ID: 41870580
If you just want to refer to the imported data with formulas, and don't need to see it, you could have the data connections on hidden sheets.
Then update will work, also when the workbook is protected.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41905434
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Wayne Taylor (webtubbs) (https:#a41869399)
-- Ejgil Hedegaard (https:#a41870580)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Ransomware continues to grow in reach and sophistication, putting data everywhere at risk. Learn how to avoid being caught in its sinister clutches with these 11 key tips.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question