links password protected sheets

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Train Strikes - 7th, 12th Nov 2024
Hide
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Links to password protected sheets

Links to password protected sheets

resolvedResolved · Medium Priority · Version 2007

Raani has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Links to password protected sheets

I have a master spreadsheet which is linked to many other spreadsheets which are password protected. Is there a way of being able to automatically update the links without putting in all the passwords on the master spreadsheet?

RE: links to password protected sheets

Hi Raani

Thanks for getting in touch. This is possible, but you'll need to use some VBA coding to achieve it.

You should record a macro of you updating the file with the password(s). You should then put this code into a Workbook_Open event.

If you need assistance with how these are put together please let me know - first you should record the macro and get the code for that.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: links to password protected sheets

Hi Gary,

Thank you for your response, I've now recorded the macro but am not sure about the next step: 'Workbook_Open event'?

Kind Regards

RE: links to password protected sheets

Hi Raani

Thanks for your reply. On reading your query again there's probably an easier way to get the updates: attach the macro you have recorded to a button. Whenever you want to update the formula you can press the button.

There are lots of objects in Excel that can take a macro, you may want to try a drawing object for example. On the Insert menu you will find a range of shapes you can draw, such as rectangles or circles as required. Click on the sheet to draw one out, and then right-click the shape. You should see the option to Assign Macro. Select the macro you recorded from this list. Now whenever you click this button the update macro will run.

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: links to password protected sheets

Hi Gary,

I've tried assigning the macro to a button but when i click it it still requests passwords to be entered. Is it the way i did the macro? When i opened the worksheet I enabled macros but didn't enable automatic update of links on the security alert. For the macro: start recording>data ribbon>edit links>'update values'>entered all the passwords>stop recording.

Thank you for your help

Raani

RE: links to password protected sheets

Hi Raani

There's a really small tweak you can make on the VBA side to push the password into macro. This will mean the password is available to anyone who has the master sheet and knows where to look.

Would this be an acceptable scenario?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: links to password protected sheets

Hi Gary,

Yes that would be fine

Many thanks

Raani

Edited on Tue 30 Apr 2013, 12:09

RE: links to password protected sheets

Hi Raani

I've attached a screenshot detailing what needs to change for your guidance. You will need to open the Visual Basic Editor. You can do this by pressing ALT + F11.

You should see a screen as attached. Double click on Module1 and you should see some code. Put a single apostrophe ' in front of the line and enter the following instead:

Workbooks.Open Filename:="C:\Users\Gary\Test Sheet.xlsx", UpdateLinks:=3, Password:="test"

Changing the filepath, name and password as required.

Save the workbook and try running your macro again.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

update-links.PNG

RE: links to password protected sheets

Hi Gary,

I followed the screenshot in your last message to test one of the links but it's causing the worksheet to open which does subsequently updates the master sheet but this won't be ideal for when i put in the code for the other linked worksheets. Is this caused by the code or have I done something in error?

There's been sone progress at least!

Thank you again for your help

Raani

RE: links to password protected sheets

Hi Raani

After some searching, there are two options that I can see. First, record the workbook being closed and include that line in the macro too. The file will be opened and then closed after updating. This seems like the most straightforward way to do this.

If you can't open the workbook then another way is with some quite complex VBA code. I haven't tried this routine myself but it is verified by third-party websites. I warn you it's fairly complex:

http://www.exceltip.com/st/Read_information_from_a_closed_workbook_using_VBA_in_Microsoft_Excel/473.html

I hope that gives you some pointers.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 8 May 2013: Automatically marked as resolved.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
advanced excel training london and other training courses.

Excel tip:

Generate randon numbers

Some types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically. Here are a few of the ways you can use it:

Type =RAND() in a cell to generate a number between 0 and 1.
Type =RAND()*100 to generate a number between 1 and 100.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.