Announcement

Collapse
No announcement yet.

update links to a password protected book!

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • update links to a password protected book!



    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!

  • #2
    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

    Comment


    • #3
      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...

      Comment


      • #4
        What mhabib is saying is that to achieve this you will need a macro.
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          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!

          Comment


          • #6
            any ideas where in the code i would put in all the worksheets passwords when it opens this workbook? Thx

            Comment


            • #7


              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

              Comment

              Working...
              X