PDA

View Full Version : VBA Help, Automatically entering passwords on multiple workbooks



Tgroove
11-01-2018, 11:12 AM
Hi All,

Please excuse my poor coding, I haven't a clue and need some help.

What it is. I have multiple documents "Work Lists" which are all protected.
I have a master document which pulls in data from the work lists and populates cells automatically.

Due to recent security we have had to password protect all the workbooks, so now when I open the master document I am being asked for the password for every workbook which has been linked to it.

Is there some sort of code I can type in VBA which will enter the password for all of them, they all are the same password.

I have managed to do it for one linked document with the code I have recorded a macro and adding a line of code but thats it, it only does it for one.
Can i repeat the line of code so it performs the same task until all the passwords have been entered? I know how many documents are protected if that helps?

here's my test code, for test purposes i have linked to 3 protected documents, this code only enters the password for one, then I have to manually enter the other two in..Also i'm doing this from my MAC at home, I will be doing it from work using PC.



Sub Button2_Click()
'
' Button2_Click Macro
'


'
End Sub
Sub Macro2()
'
' Macro2 Macro
'


'
Workbooks.Open Filename:="Macintosh HD:Users:user: Desktop:MASTER.xlsx" _
, UpdateLinks:=3, Password:="test"

End Sub


Hope someone can help, been pulling my hair out!

Best,

Trevor

Jan Karel Pieterse
11-02-2018, 05:46 AM
Like so:

Sub Button1_Click()
Dim ExcelLinks As Variant
Dim Ct As Long
ExcelLinks = ThisWorkbook.LinkSources(xlExcelLinks)
For Ct = LBound(ExcelLinks) To UBound(ExcelLinks)
Workbooks.Open ExcelLinks(Ct), , , , "test"
Next
End Sub

Tgroove
11-02-2018, 06:57 AM
Like so:

Sub Button1_Click()
Dim ExcelLinks As Variant
Dim Ct As Long
ExcelLinks = ThisWorkbook.LinkSources(xlExcelLinks)
For Ct = LBound(ExcelLinks) To UBound(ExcelLinks)
Workbooks.Open ExcelLinks(Ct), , , , "test"
Next
End Sub


Thank you for this information, but I'm such a novice at this. Can you let me know where I am supposed to enter my links?

Jan Karel Pieterse
11-02-2018, 07:44 AM
The links are in the file. This code assumes it is in the same file as where the current links are (hence "Thisworkbook").