VBA to refresh vlookup values from password protected workbook

Hi,

I currently have a master workbook that is password protected and only accessible to a few people, and a general workbook that has a lot of vlookups into the master to retrieve and present data that is open to everyone.

When I open the general document I'm prompted to update links and clicking Yes returns an error that the links can't update. What I have to do to update the general document is;

1. Open it, choose to not update the links
2. Go to Data > Edit Links > Open Source
3. Open the master document as read only
4. Close the master document without saving changes
5. Click Check Status in the general document
6. Wait for OK to be returned in the status field

The functionality I would like is;

1. Open the general document and not be prompted with the standard Excel ‘update links’ dialogue box
2. Have a button for users to click that will open the master document in read-only mode in the background so it’s not visible to users
3. Automatically input the password, update the connection status or similar to refresh the vlookup values
4. Close the master document without saving changes

Would it be possible to build this functionality in Excel VBA then attach to a button?

Thanks in advance!
Adam

You can do better than a button, you can add code to the workbook open event to do this automatically when a user opens the workbook.

And what you actually want there can be recorded with the macro recorder and aid your learning rather than have someone else write it all for you. Go do that, post the resulting code it'll write for in here and we'll have a looksee, clean it up and make it all happen in the background invisibly to the user for you.

AAAAAAAARRRRRRRGH. Another one I try to persuade to try learning something who isn't going to now...

No point it trying to teach a runner how to walk

Thanks very much guys, the code for an update button works a treat!

I've tried a couple of different things within the workbook open event and I'm still getting the message pop-up... things I've tried are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I've also disabled hints from the Data > Edit Links option box. One anomoly is when looking in File > Options > Advanced > General > Ask to update automatic links.... I've tried un-checking this option then saving the workbook but the next time it opens the update links box reappears.

Not sure if this is related or not, nor how to force that checkbox to remain empty..?

Thanks
Ada

I think that in the link I provided, you will need to use the middle option at step 3, 'Don’t display the alert and don’t update automatic links'.

Check the box in excel options first, if I've read it correctly, unchecking it is probably overriding, or conflicting with the setting on the workbook.

Trying it with vba won't work if it's in the same workbook, it would need to be in a different workbook and in code that is going to open the workbook where you want the warning suppressed.