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!

 

[Andrew Poulsom]

When you open the target workbook, say No to update links if prompted. Then try this code:

Sub UpDateLinks()
    Const PWord As String = " "
    Dim xlLinks
    Dim i As Integer
    xlLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(xlLinks) Then
        For i = 1 To UBound(xlLinks)
            SendKeys PWord & "{Enter}"
            ThisWorkbook.UpdateLink Name:=xlLinks(i)
        Next i
    End If
End Sub

Change the PWord constant to suit.

 

[pangster]

Andrew

That works an absolute treat! thank you very much..

I do encounter and error though if the linked workbook (source) is already open though.. is there a way around this?

can you also explain to me why you select no when prompted to update link sources? - i just want to understand why you select no - but it still works??

thanks again for your help! :wink:

 

[Andrew Poulsom]

Andrew

That works an absolute treat! thank you very much..

I do encounter and error though if the linked workbook (source) is already open though.. is there a way around this?

can you also explain to me why you select no when prompted to update link sources? - i just want to understand why you select no - but it still works??

thanks again for your help! :wink:

Try:

Sub UpDateLinks() 
    Const PWord As String = " " 
    Dim xlLinks 
    Dim i As Integer 
    xlLinks = ThisWorkbook.LinkSources(xlExcelLinks) 
    If Not IsEmpty(xlLinks) Then 
        On Error Resume Next
        For i = 1 To UBound(xlLinks) 
            SendKeys PWord & "{Enter}" 
            ThisWorkbook.UpdateLink Name:=xlLinks(i) 
        Next i 
    End If 
End Sub

If you say Yes to update links Excel asks you for the passwords. I thought that's what you were trying to avoid.

 

[pangster]

andrew - thats fantastic! - thanks again..

i just wondered why you select no - but your right i'm trying to avoid passwords!! thanks again for your help!

 

[pangster]

andrew

sorry to be a pain.. when opening the workbook with the source also opens.. it dumps the password into the first linked cell on the target workbook?? - is there a way to avoid this?

 

[Andrew Poulsom]

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

 

[TLA]

multiple files

Any idea how to do this with multiple files. The sendkeys would need to be in order. I suppose I could enter them all static but it would fail any time an external report was added or subtracted.

 

[Andrew Poulsom]

That code works with multiple files. But the password has to be the same for each file.

 

[TLA]

I meant multiple passwords. I have 20 files with 20 different passwords. That means you can just enter 20 password variables and send them in order. That will onyl be an issue when they change. I'll probably read in the passwords from the master file and keep them in a list there for ease of editing.