Access password protected files from another worksheet RRS feed

  • Question

  • I have 15 password protected workbooks that use the same password.

    I have another workbook called "Checks" that validates whether specific cells contain text or numbers in the sheets of the protected workbook:

    (=if(isnumber([WorkbookA.xlsx]Sheet1'!A16),"OK","missing number")) 

    When I open the "Checks" workbook and click on update, it prompts me 15 times for the password.

    Is there a way I can pre-set the password in the "Checks" file (maybe in a macro in ThisWorkbook) so that I don't have to re-type the password each time?

    I would like to avoid having to open each protected workbook first.

    Tuesday, March 28, 2017 6:15 PM


  • Hi xanthman4,

    you can certainly do this with VBA code.

    there are 2 options for you.

    (1) you can try to use Workbook.open method , which allows you to enter password as an parameter. so you can pass the password in code.

    Workbooks.Open Method (Excel)

    (2) you can use Wprkbook.unprotect method. which you can use after opening a workbook to unprotect it.

    Workbook.Unprotect Method (Excel)

    based on your requirement, I suggest you to choose 1st approach to solve your issue.

    you can try to put all your workbooks in one folder.

    then you can try to execute code from "Checks" Workbook.

    that will loop through all the Excel files in that folder and open it programmatically.

    below is the Example:

    Sub demo()
        Dim xStrPath As String
        Dim xFileDialog As FileDialog
        Dim xFile As String
        On Error Resume Next
        Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
        xFileDialog.AllowMultiSelect = False
        xFileDialog.Title = "Select a folder"
        If xFileDialog.Show = -1 Then
            xStrPath = xFileDialog.SelectedItems(1)
        End If
        If xStrPath = "" Then Exit Sub
        xFile = Dir(xStrPath & "\*.xlsx")
        Do While xFile <> ""
            Workbooks.Open Filename:=xStrPath & "\" & xFile, Password:="1234"
            xFile = Dir
    End Sub

    you can see that I pass the password "1234". you just need to change it with your password. so it will work for all your files.

    if you don't want to write password in code then you can also store this password in worksheet and then fetch the value of password in code and you can make that sheet hidden to make your password secure.

    if you don't want to store your password anywhere and want to enter it when you run your code for only one time for all the workbooks then this is also possible.

    you just need to use inputbox in code. it will ask password and you can store it in variable and then use that variable every time.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 29, 2017 1:18 AM