Linking to password protected workbook

[pangster]

Hi,

Please can somebody help me with this problem?

I have a workbook (a summary of performance) which pulls data back from several other workbooks via linked cells (all these workbooks are password protected) - all have the same password.

The problem I have is that everytime the summary workbook is opened or tries to pull back data from one of these other workbooks - I'm prompted for a password.. is there a way to remove this or automate this .

Thanks!

 

[Zanatos1986]

We'll have to avoid the Sendkeys if the source is open:

Sub UpDateLinks()
    Const PWord As String = " "
    Dim xlLinks
    Dim i As Integer
    Dim wb As Workbook
    Dim wbOpen As Boolean
    xlLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(xlLinks) Then
        For i = 1 To UBound(xlLinks)
            wbOpen = False
            For Each wb In Workbooks
                If wb.FullName = xlLinks(i) Then
                    wbOpen = True
                    Exit For
                End If
            Next wb
            If wbOpen = False Then
                SendKeys PWord & "{Enter}"
                ThisWorkbook.UpdateLink Name:=xlLinks(i)
            End If
        Next i
    End If
End Sub

This would work for what I am doing except I have to update my links. I know you say hit don't update but if I don't my data won't transfer over? This makes doing this a mute point... So, is there a way to update the links AND have the password pop-up disabled (like autogenerate what the password was into the pop-up or something)?
Please let me know any suggestions!

Thanks

 

[Zanatos1986]

I have to be able to select "Update" to update my sheet without prompting for a password and I also have to be able to hit "Don't Update" to make it where I can go back and look at old data.

 

[DarkoDeign2]

We'll have to avoid the Sendkeys if the source is open:

Sub UpDateLinks()
    Const PWord As String = " "
    Dim xlLinks
    Dim i As Integer
    Dim wb As Workbook
    Dim wbOpen As Boolean
    xlLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(xlLinks) Then
        For i = 1 To UBound(xlLinks)
            wbOpen = False
            For Each wb In Workbooks
                If wb.FullName = xlLinks(i) Then
                    wbOpen = True
                    Exit For
                End If
            Next wb
            If wbOpen = False Then
                SendKeys PWord & "{Enter}"
                ThisWorkbook.UpdateLink Name:=xlLinks(i)
            End If
        Next i
    End If
End Sub

Sorry for a newbie question. Is it possible to have example on how to write the links and such?
How would this code look like if:
Password = 123Goodday
Workbook name = excel_beginner_school.xslm
Link = C:\Documents\My projects\New