Consulting

Results 1 to 4 of 4

Thread: VBA Help, Automatically entering passwords on multiple workbooks

  1. #1
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    3
    Location

    VBA Help, Automatically entering passwords on multiple workbooks

    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

  2. #2
    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
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    3
    Location
    Quote Originally Posted by Jan Karel Pieterse View Post
    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?

  4. #4
    The links are in the file. This code assumes it is in the same file as where the current links are (hence "Thisworkbook").
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •