Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Maintaining spreadsheet security whilst performing data connection refreshes

Posted on 2016-11-01
3
Medium Priority
?
43 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 1000 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 23

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 1000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you're a modern-day technology professional, you may be wondering if certifications are really necessary. They are. Here's why.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

715 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