Solved

Maintaining spreadsheet security whilst performing data connection refreshes

Posted on 2016-11-01
3
22 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
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 21

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
macro for each dropdown 15 44
splitting text of cell to columns 14 24
COPY from excel to notepad 3 33
Move entire row to another workbook based on cell value 20 41
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
A customer recently asked me about anti-malware and the different deployment options available for his business. Daily news about cyberattacks, zero-day vulnerabilities, and companies that suffered a security breach made him wonder if the endpoint a…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now