update links to a password protected book!

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi,,
    I have a workbook (asdf.xls), that has values from multiple password protected workbooks.. When the asdf.xls is opened it obviously asks if you want to udpate and if I hit yes, I have to enter every password.. Is there a way around this? I don't want to remove the passwords from the workbooks so others can't see info except for the person who's supposed to. But the workbook that has all the linked values in it is to be viewed by a manager, that doesn't want to open enter 30+ passwords every time it's opened, but does want it to update. Any help is appreciated. Thanks!

    Hello jjst34,


    You could do try opening the linked files in your macro. In that case the passwords could be included in the OPEN commands. Since the linked files would already be open, the user will not be required to provide the passwords for updating the links.


    M

    Quote

    Originally posted by mhabib
    Hello jjst34,


    You could do try opening the linked files in your macro. In that case the passwords could be included in the OPEN commands. Since the linked files would already be open, the user will not be required to provide the passwords for updating the links.


    M


    there is no macro though.. it is just a cell formula that says =+G:\budgets\blah\blah.xls


    but when the workbook is open it want's to update.. which I want.. I just don't want to be required to enter the pass..


    maybe i am not following...

    ahh ok.. any ideas on how to get started on this macro? I'm assuming it is to be in the workbook_open() but not sure what to put.. i did that
    Application.AskToUpdateLinks = False


    but now i need to tell it how to put in the 33 workbook passwords.. not a clue how to do that.. any help is appreciated. Thanks!

    Dear jjst34,


    Sorry for disappearing, but was too tied up. Thanks to royUK for clarifying my suggestion. You're right about the OPEN event - that's where the macro code needs to be. The setting for Not updating links also looks fine. Here's the code you asked for.


    First, you'll need the names of all the files you are using - including full path names.


    For each of these files you'll need to use the Workbooks.Open command


    Eg, if you have a file
    C:MypathFirstFile.xls,


    With the password
    abc


    Then the command to open this file would be


    Workbooks.Open FileName := "C:MypathFirstFile.xls", Password:= "abc"
    ...
    And so on for each file.


    This method will involve a lot of typing for 33 files. A better way would be to dump all your file names into Excel, and then use concatenation to build your Workbooks.Open commands. These can then be copy/pasted into the Visual Basic Editor to save on keyboarding.


    Let me know if you need further help with this.


    Regards,


    M


    :spin:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login