Multiple Spreadsheet Links with Different Passwords

shobolos

Active Member
Joined
Sep 7, 2010
Messages
274
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
Hi, Welcome to the board :)

Can you adapt this macro:
Code:
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
ABC
1PathWorkbookPassword
2D:\PROFILES\xxxx\My Documents\TempFile B1B1
3D:\PROFILES\xxxx\My Documents\TempFile B2B2
4D:\PROFILES\xxxx\My Documents\TempFile B3
5D:\PROFILES\xxxx\My Documents\TempFile B4B4
Workbook Data
Excel 2003

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

Forum statistics

Threads
1,136,275
Messages
5,674,782
Members
419,524
Latest member
helensesc

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
Top