Query External Data and protect

Is there somebody who is "overlord" over the managers? That is someone who is allowed to see all the data for all the departments?

Also how much security is needed? I'll get paranoid here and assume that maximum security is needed and that an overlord exists.

The overlord will need to know enough excel to be able to punch buttons on a spreadsheet.

On the overlord’s spreadsheet, use MS-Query to import all the data.

Have a pivot table that has only the manager’s names on it and overlay this with a named dynamic range. This will produce a unique list of manager’s names that will be current with the data. No list for the overlord to manage.

Then write a macro that loops through this list of names.

Develop a pivot table that has all the data you need, and put a filter on it with the manager’s name.

Overlay the results of this with a named dynamic range.

Copy this range to a table. (I have some good table clearing code – see bottom of post).

Make a copy of the file and rename it with the manager’s name (and date stamp if you wish).

Delete the master data page and any other pages that aren’t needed and hide any pages that need to be hidden (like the raw data page).

Save and close the file and move onto the next manager on the list.

There is no way a manager can get another manager’s data under this system unless the overlord sends the manager the wrong spreadsheet.

That will probably be the worst part of the job for the overlord: distributing the reports. For this purpose, I would produce a table with manager names and let the overlord fill in the email addresses (using data validation drop down list from manager names).

Then write a macro to read the list, the emails and email the reports with another click of the button or perhaps email them as they are produced.

I would even be better to have the Windows Task Scheduler run the report automatically (I have instructions for that too). However, it would have to be run from someone’s account. Because it involves Outlook Exchange (I presume), then the user needs to be logged in (although the machine can be locked) Outlook has to be installed (but does not have to be up and running) and if the user’s password changes, the user will have to go back in and change the password in the Task Scheduler (which isn’t difficult to do – the user can blindly follow the steps without knowing what’s happening).

I have an old laptop sitting in my cube and this is its sole purpose. It stays awake at night and mails reports to people.

Here's the clear table code. You pass it the sheet on which the table resides and the table name. It clears out any filters and removes the data if there is data to be removed.

Please Login or Register  to view this content.