Multiple Spreadsheet Links with Different Passwords

[shobolos]

I have a spreadsheet that is linked to many other spreadsheets. All these 'other' spreadsheets have different passwords to one another.

How can I update my spreadsheet from all of the 'other' spreadsheets without having to MANUALLY enter all of the passwords of the 'other' spreadsheets?

I presume there is a script/macro/VBA that can be created with all the passwords listed against their relevent spreadsheet, but I just don't know how do it. Help please!!

Please note that if I go to 'Update Values' for all of the 'other' spreadsheets then Excel ask me for a password for each and everyone, which is extremely labourious.

 

[al_b_cnu]

Hi, Welcome to the board

Can you adapt this macro:

Option Explicit

Sub MergeWorkBooks()
Dim lRowEnd As Long, lRow As Long, lCurRow As Long
Dim sCurFile As String
Dim vaData As Variant
Dim wbCur As Workbook
Dim wsData As Worksheet, wsMerge As Worksheet

Set wsData = Sheets("Workbook Data")
Set wsMerge = Sheets("Sheet1")

'** Get Workbook information data into array **
lRowEnd = wsData.Cells(Rows.Count, "B").End(xlUp).Row
vaData = wsData.Range("A1:C" & lRowEnd).Value

lCurRow = 1
Application.EnableEvents = False
For lRow = 2 To UBound(vaData, 1)
    sCurFile = CStr(vaData(lRow, 1))
    If sCurFile = "" Then sCurFile = ThisWorkbook.Path
    sCurFile = sCurFile & Application.PathSeparator & CStr(vaData(lRow, 2))
    If LCase$(Right$(sCurFile, 4))<> ".xls" Then sCurFile = sCurFile & ".xls"
    Set wbCur = Workbooks.Open(Filename:=sCurFile, ReadOnly:=True, Password:=CStr(vaData(lRow, 3)))
    lCurRow = lCurRow + 1
    wsMerge.Range("A" & lCurRow).Value = wbCur.Sheets(1).Range("A1").Value
    wbCur.Close
Next lRow
Application.EnableEvents = True

End Sub

which reads workbook data from here:

Excel Workbook
	A	B	C
1	Path	Workbook	Password
2	D:\PROFILES\xxxx\My Documents\Temp	File B1	B1
3	D:\PROFILES\xxxx\My Documents\Temp	File B2	B2
4	D:\PROFILES\xxxx\My Documents\Temp	File B3
5	D:\PROFILES\xxxx\My Documents\Temp	File B4	B4
Workbook Data

Excel 2003

and copiesthe contents of cell A1 from the first sheet of each w/book to sheet 'Sheet1"