VBA Updating Multiple Links that are Password Protected

Question

Can someone please help me? I'm trying to program a macro that pulls data from a workbook that is linked to multiple different workbooks that's providing it data which are all password protected (same password). How do I write in VBA to update all links and provide password for each prompt automatically?

DataPullMacroExcel =Link=>
   CentralExcelWB =Link=>
      Workbook1 (Password)
      Workbook2 (Password)
      Workbook3 (Password)
      Workbook4 (Password)

Answer 1

There is no specific method to update the links within a workbook and provide the respective passwords, you can first open the source workbooks (providing their passwords), and then open the main workbook, so all linked workbooks gets updated and no password prompts will appear because they're already open.

Sub UpdateLinkedWorkbooks()

    'First, you need to open all password-protected workbooks that are
    'linked to the MainWorkbook.xlsx file.
    Workbooks.Open FileName:="C:\Workbook1.xlsx", Password:="Password"
    Workbooks.Open FileName:="C:\Workbook2.xlsx", Password:="Password"
    Workbooks.Open FileName:="C:\Workbook3.xlsx", Password:="Password"
    Workbooks.Open FileName:="C:\Workbook4.xlsx", Password:="Password"

    'Second, you need to open the MainWorkbook.xlsx file, setting the
    'UpdateLinks parameter to 3, that means external references will 
    'be updated when the workbook is opened.
    Workbooks.Open FileName:="C:\MainWorkbook.xlsx", UpdateLinks:=3

    'Third, you can close all password-protected workbooks
    Workbooks("Workbook1.xlsx").Close False
    Workbooks("Workbook2.xlsx").Close False
    Workbooks("Workbook3.xlsx").Close False
    Workbooks("Workbook4.xlsx").Close False

    'Finally, do whatever you need with MainWorkbook.xlsx data

End Sub