Solved

Protect an Excel SpreadSheet and allow access to an external data source

Posted on 2014-04-03
5
3,379 Views
Last Modified: 2014-04-03
Custom reports from an Access database tables are a great solution for quickly forming the results in a way that can be sorted, filtered and even creating pivot reports.  We're getting a sweet tooth for this technique and it's going well.

But, the SpreadSheet can inadvertently be changed or otherwise mangled and then saved.

I know the deal with protecting Excel, etc. but this breaks the data refresh-ability.  I'm no VBA jockey and can handle macro's, etc. but not proficient or practiced.

Is there a GUI kind of way to allow the SpreadSheet to open and refresh and read the data and allow users to sort, filter, etc., but otherwise protect the SpreadSheet (especially the formulas)?
0
Comment
Question by:VirtualKansas
[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
  • 2
5 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39976285
Is there a specific trigger that will allow the refresh?  For example, does it happen when the user clicks a button?

If so, you can quickly record a macro to disable protection then turn it back on.
After that, copy the recorded code into the button's procedure and insert your refresh code between those lines.
0
 

Author Comment

by:VirtualKansas
ID: 39976387
The trigger is when the Excel file opens (really don;t need the user to hit the data refresh button.)

Recording a macro is easy enough; been years since I've don it.  Mind a little refresher on how to embed the macro into a button?  Also note; SpreadSheet has four worksheet tabs, so would button have to go in a worksheet?

I think the best outcome would be to have the macro run as part of file open and let user just close & open if they want to refresh.  No button needed, then...
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 39976648
There are a few ways you can handle a "button".

You can drop a button on top of any sheet.  That button is then part of that sheet and is transportable (anyone who uses the spreadsheet or a copy of it will see the button.)

You can create a link in the title bar of Excel.  This is typically not transportable and is specific to the person who added that bar-button.
You can add a shortcut key; that's also typically attached to the user, not the workbook.

You can add a new ribbon bar group and include it in that group. (I'm not sure if that's transportable since I've never tried it.)

You can create an add-in (which is probably more trouble than it's worth for something so simple.)

You can also simply leave the macro as user-selectable.  To do that, press Alt-F8 and find the macro and run it.  Anyone can do that with typical macros, as long as macros are enabled when the user opens the workbook.

---
As you were saying, it seems that you want the macro to run on opening the workbook.  To do that, go to the VBA area (Alt+F11), in the top-left panel (Project window) double-click on "ThisWorkbook" to open the code window for the workbook.
At the top-left of the code window, choose dropdown item "Workbook" and you'll get this code block:

Private Sub Workbook_Open()

End Sub

Open in new window

Inside there you can put the code to unprotect, refresh and protect.
And if the users know about Alt+F8, they can run a refresh if you put it in a different macro area, so something like this:

Private Sub Workbook_Open()
   ' unprotect
   RefreshData
   'protect
End Sub

Sub RefreshData()

End Sub

Open in new window

0
 

Author Closing Comment

by:VirtualKansas
ID: 39976765
Really thorough hand holding response, appreciated.  I'm not really that much of a N00B, but it's been a long day/week and head get's thick after a while.  Small details in response made things much quicker to try.  Thanks & regards...
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39976943
Glad to help.
I always figure that extra info is also good to people looking in later :)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

751 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