Results 1 to 4 of 4
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update links from password protected workbooks (07

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

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update links from password protected workbooks (07

    (Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)

    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>post 675,462</post:>) 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
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update links from password protected workbooks (07

    You can use the "UpdateLinks" parameter in the OPEN command

    From XL 2002 VBA Help on Workbooks.Open

    <hr>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 <hr>

    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

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update links from password protected workbooks (07

    Thanks to all!

    I got it to work.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •