• Update links from password protected workbooks (07

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Update links from password protected workbooks (07

    Author
    Topic
    #446140

    Why don’t you open all 25 workbooks, update all links, then close the workbooks?

    Viewing 0 reply threads
    Author
    Replies
    • #1083607

      (Edited by HansV to provide link to post – see Help 19)

      I have a project I have been asked to complete. We have a folder that contains about 25 workbooks that are each protected by their own unique password. I have been asked to create a summary workbook that contains the data from certain cells of each of these individual workbooks.
      I have created a workbook with links to the data that I need to summarize, but each time I open the workbook I am asked to supply the password for each of the 25 files.
      I found some code (post 675,462) in the lounge that will allow me to open each of the files, using a for/next loop but I am stumped on how to force the individual links to update while the workbook is open. Is there a piece of code that I can insert before the wkb.Close that will force the link to that workbook to update?

      Thanks in advance for any help you can provide. The code I am using is below.

      Private Sub Workbook_Open()
      Dim wkb As Workbook
      Dim rng As Range
      Dim rCell As Range
      Dim sPath As String
      Dim sFile As String
      Dim sPassword As String
      Dim sSummary As String

      ‘set range for the path
      Set rng = Range(“B:B25”)
      Set sSummary = “Monthly_review_linked.xlsm”
      For Each rCell In rng
      sPath = rCell.Value
      sPassword = rCell.Offset(0, 1).Value
      sFile = rCell.Offest(0, 2).Value
      Set wkb = Application.Workbooks.Open( _
      Filename:=sPath, Password:=sPassword)

      wkb.Close (False)
      Next
      Set rCell = Nothing
      Set rng = Nothing
      Set wkb = Nothing
      End Sub

      • #1083795

        You can use the “UpdateLinks” parameter in the OPEN command

        From XL 2002 VBA Help on Workbooks.Open

        [indent]


        UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

        Value Meaning
        0 Doesn’t update any references
        1 Updates external references but not remote references
        2 Updates remote references but not external references
        3 Updates both remote and external references


        [/indent]

        So you would do something like:
        Set wkb = Application.Workbooks.Open( _
        Filename:=sPath, Password:=sPassword, _
        UpdateLinks:=3)

        to Update both remote and external references when each file is opened.
        Steve

    Viewing 0 reply threads
    Reply To: Update links from password protected workbooks (07

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: