Refresh links from Microsoft Teams password protected files

[Bal_Ocado]

Hey Guys,

Hoping you can help me, been stuck on this all week and I'm not how to work it through!

I have a Microsoft Teams folder in which there exists c.15 files for individual countries (file names: Australia, Austria etc.) and a consolidation file in the same folder

All of the country files have a different password encryption (which I have to hand) but I'm not able to refresh links in my consolidation file to pick up the latest numbers in all the other files without inputting each password every time.

Is there a VBA / Macro I can implement here to refresh my links to all of the country files, in my consolidation without opening them and typing the password every time?

Thanks in advance!


EDIT 1:

Private Sub Workbook_Open()
Application.DisplayAlerts = False
ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
Application.DisplayAlerts = True

If MsgBox("Do you want to update links?", vbYesNoCancel, "Update LInks") = vbYes Then
Set wbk = Workbooks.Open(Filename:="https: //insidemedia.sharepoint.com/sites/CreativeSystemsFY/Shared Documents/General/May/[Australia.xlsx]", Password:="Australia1", UpdateLinks:=1)
wbk.Close False
End If
End Sub

the VBA i have.. but when actioned, it mentions it can't locate the file?


EDIT 2:

I also tried to record a macro, but to avail..

ActiveWorkbook.UpdateLink Name:= _
"https://insidemedia.sharepoint.com/sites/CreativeSystemsFY/Shared Documents/General/May/Australia.xlsx" _
, Type:=xlExcelLinks
ActiveWorkbook.UpdateLink Name:= _
"https://insidemedia.sharepoint.com/sites/CreativeSystemsFY/Shared Documents/General/May/Austria.xlsx" _
, Type:=xlExcelLinks