Linking to password protected workbook

P

pangster

Board Regular
Joined
Jun 15, 2005
Messages
160
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 :confused: .

Thanks!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
When you open the target workbook, say No to update links if prompted. Then try this code:

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.
 
Upvote 0
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: :LOL: :cool:
 
Upvote 0
pangster said:
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: :LOL: :cool:
Click to expand...

Try:

Code: 
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.
 
Upvote 0
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!

:biggrin:
 
Upvote 0
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?
 
Upvote 0
We'll have to avoid the Sendkeys if the source is open:

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Unprotect Sheet code runs, but doesn't unprotect sheet
Replies
3
Views
108
RockandGrohl
R
N
  • Question
Migrating a data pull
Replies
0
Views
80
Natemoney2
N
SaraWitch
  • Question
Unprotect/protect all worksheets with a checkbox
Replies
3
Views
561
skillilea
S
A
  • Question
VBA to run in Protected Workbook
Replies
5
Views
182
bobsan42
bobsan42
H
  • Question
Macro-enabled workbook problems
Replies
0
Views
189
helplessnoobatexcel
H

Forum statistics

Threads
1,214,614
Messages
6,120,533
Members
448,969
Latest member
mirek8991

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top