Connection and Protection

[andyoungy]

Is it possible to have a connection (to another workbook sat in a network drive) set and then have a protection in place to stop any user editing the data or changing the auto-filter?

I am setting up a new reports for multiple users around an estate, I want them all to connect to a workbook sat on a network drive and bring in the data. I then need to have autofilters on the new report so users can only see the data for their branch and a protection in place so they cannot change the location (as the connection will be bringing in all the data from the main sheet in the network drive?

I can't get the connection to work in the individual reports if the protection is in place. Or if anyone has any other options that would work better than a connection?

[ARGK]

Hi Andy
Suggest you set up an Auto_Open macro that runs when you open the workbook. This macro should turn protection off when you open, calculate the worksheet and then reassign protection.
Hope this helps.
Tony

[andyoungy]

Thanks Tony,

Something I have never done before but worth a try! I have some users on office 2003 so would a auto_open macro still work with them? I know the connection works that I have set up as I have adjusted to query to suit but never tried a macro.

Thanks
Andy

[andyoungy]

Thanks Tony,

Something I have never done before but worth a try! I have some users on office 2003 so would a auto_open macro still work with them? I know the connection works that I have set up as I have adjusted to query to suit but never tried a macro.

Thanks
Andy

[ARGK]

Hi Andy
Auto_Open macros should work on Office 2003. If you have any issues with the Macro then come back to the Forum.
Tony

[andyoungy]

Hi Andy
Suggest you set up an Auto_Open macro that runs when you open the workbook. This macro should turn protection off when you open, calculate the worksheet and then reassign protection.
Hope this helps.
Tony

Hi Tony,

I have tried to set up a Marco in the file I need to bring in the date and recorded going to the protected file, adding the password and I have added a button to run the macro but it is still asking for the password to refresh the data. Is that because I need to completely remove the protection in the master file and refresh the date and put the password back on when do the macro?

Thanks
Andy

[ARGK]

Hi Andy
When you access the protected workbook/worksheet you should remove protection, copy the data and then re-apply the protection all from within the Auo-Open macro.
Hope this helps.
Tony

[andyoungy]

Sorry I am still struggling to get my head around this, the protection is a password protection to access the file done when saving the file to the drive. I need this as it stops anyone getting in to the master file on our network drive.

Therefore would it require re-saving to change the password so the macro would have to record me saving it? But the users that run the file will run the macro when open it but they won't have the privileges to save the file to the network drive and remove the protection and then add the protection and save again to put back in place through the macro?

[ARGK]

Hi Andy
Can you write down what needs to happen step by step starting with the current satus of your Master File and I will see what I can come up wth tomorrow sometime.
Thanks
Tony

[andyoungy]

Ok, I will try and explain it as best I can, sorry if I get myself confused:

Master File is saved in a network folder, this is saved using the tools option to add a password protection to stop anyone without the password being able to see the data within. (this is a must) The Master File has numerous tabs with data for each retail outlet we have and we dont want other retail outlets seeing each others data.

Each outlet will have their own excel file, saved to their C:Drive. They all have access to the network folder where the master is saved (but they only have permissions to read what is in the network folders, sub-folders and files, not to save anything in there. The retail outlets individual file needs to read only their tab from the master file. it needs to bring in everything in there so that when one person updates the Master the retail outlet can open their individual file and see the data refresh automatically (or after pressing a button to refresh, which I tried when trying a macro)

I have tried to set up and connection to the master so it copies the whole tab in and refreshes every time the individual file is opened and I have just used a formula to link to the relevant section on the tab with a macro to refresh the links and data when they click on it. The formula I have in there at the moment reads like this:

='R:\Manufacturing and Operations\Stock & Priorities\Lecia - Artwork Allocated\[Artwork Allocated.xls]Glasgow'!F2

And the Macro I set up, with me trying to refresh the links, adding the password for the master file, looks like:

Sub Macro1()
'
' Macro1 Macro
'


'
ActiveWorkbook.UpdateLink Name:= _
"R:\Manufacturing and Operations\Stock & Priorities\Lecia - Artwork Allocated\Artwork Allocated.xls" _
, Type:=xlExcelLinks
End Sub

When I try and run the macro it still asks for that password.

So I need the Individual file to:

- Look into the master
- pass the protection/security (which needs to be good enough to stop anyone else from seeing any data in the file) either remove and then replace or just enter the password, provided others cant see the password
- copy all the data in a specific tab
- paste into the right section in the individual file
- put the protection back on the master sheet, if the process had removed it

[ARGK]

Hi Andy
Now I understand your issue :-).
This solution is based on the fact that you have already copied and used "Paste Special - Paste Links" in each of your Outlet workbooks from the Master.
In each of your Outlet Workbooks do the following:
1. Open the Ouotlet workbook.
2. Enter Alt+F11 to open the VBA Editor Module.
3. Double click on "ThisWorkbook" and paste the code below into that sheet.
4. Click save.
5. Right-click on "ThisWorkbook" and choose VBAProjectProperties.
6. Click on the Protect tab.
7. Click on "Lock Project for Viewing".
8. Enter a password to protect code from viewing (because it contains the password to the master file).
9. Click OK.
10. Close the VBA window.
11. Click Save again.

Note: when you open each Outlet workbook you will still be asked for a password - click cancel each time (I havent worked out how to hide that prompt yet).
The code below will deal with opening the Master, allowing the links to update and closing it straight afterwards.

Please Login or Register  to view this content.

Hope this helps.
Tony

[andyoungy]

Hi Tony,

I have finally got around to playing about with the macro and works great, thank you. I have used it already in a couple of files where I need it.

Thank you for all your help