Links to Restricted Workbooks

I am assuming that the file that has the data you want is in a directory to which your users don't have access and you want some of them to "cherry pick" specific pieces of data from this file.

The trick is to get them to use your credentials to get to the file.

How often is the source file updated? Is it updated on a schedule? Do you have control over the source file?

I can think of ways of using hidden access lists and passwords and MS-Query to get the data, but the list can be hacked by someone with enough skill. The query can be set up to run every x minutes.

I can also think of a way where they click a button and it sends an email to a mailbox, the mailbox checks that the sender is on the authorized list and mail backs the report with the current value of the links. The issue with this is it isn't "real time."

Maybe the best alternative is to set up yet another folder: one that has less restrictive access and to have the Windows task scheduler or an event in the source program itself, write the extracted data there and those people with special access can see it.

Do the users who have limited access get a different version of the file from those who have no access at all?

You say that you enable the content on the summary file. Is this your copy? If so, how do the limited access people get hold of it? How do they use the information: do they merely read it or do they modify it? Does any information on what they fill out have to make it back to the source spreadsheet?

I can propose something else. I have a piece of "standard" code that mails an attachment to a mail recipient. With small modification it can mail to a list. So you can have the Windows Task Scheduler run the program in the background for you - it will mail a copy of the report, updated with your credentials to the list of recipients. I can save a copy of the file and break the links so they have the current value for the link at the time of mailing - or if they are merely reading it, we can save it as a PDF, we could mail it out several times a day!

I can give you step-by-step instructions on how to set up the Task Scheduler - I have a tool to help and it's not that difficult to do.

The MS-Query method would take a small bit of code on the source file to put the data in a format MS-Query can understand. I doubt you have it already in neat columns. This can be tied to a workbook event that updates the file every time the source workbook is save. Should take a fraction of a second to run. I'll also give you step-by-step instructions on setting up MS-Query.

This is the way I would set up the system, so tell me if this would work operationally for you.

The managers have access the the file in the restricted directory. They will also need access to the same non-restricted directory as the people who only need the summary.

When a manager updates the report and exits Excel, a close workbook event extracts the summary information to another workbook and copies that workbook to the non-restricted directory. Depending on how complicated the extraction process is, this may take several seconds or longer for the file to close.

So the summary file will be kept updated at all times.

This is about as far as I can take it without a sample manager file and a sample summary file. Fill in the files with a dozen or so dummy records and attach here.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.