Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Pass A Password To A Protected Workbook

This is a discussion on Pass A Password To A Protected Workbook within the Excel Questions forums, part of the Question Forums category; Unlike the posts that I've located on the site, I actually know the password of the workbook I'm trying to ...

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    San Diego
    Posts
    154

    Default Pass A Password To A Protected Workbook

    Unlike the posts that I've located on the site, I actually know the password of the workbook I'm trying to open! My question is how to pass that password to the workbook in my code so that the user is not bothered with having to enter in the password (I don't want them to know it anyway).

    I tried this combination but that didn't do the trick:

    Workbooks.Open Filename:="myworkbook.xls"
    ActiveWorkbook.Unprotect Password:="abcd1234"

    Somehow I need to open the workbook and pass the password at the same time.

    Thanks for you help!

    M

  2. #2
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default Re: Pass A Password To A Protected Workbook

    Markkramer,
    you can put this in the workbookmodule
    Code:
    Private Sub Workbook_Open()
    ActiveWorkbook.Unprotect Password:="abcd1234"
    End Sub
    It would be good to add this too
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Protect Password:="abcd1234"
    End Sub
    regards,
    Erik

  3. #3
    Board Regular
    Join Date
    May 2002
    Location
    San Diego
    Posts
    154

    Default Re: Pass A Password To A Protected Workbook

    Thanks, Erik.

    If I understand what you're suggesting, then when the subroutine reaches my code that has the "workbooks.open" command it will automatically know to read the Private Sub Workbook_Open() routine that you've listed in the calling workbook that you've listed.

    (let me know if I'm wrong here!)

    If so, that may create some problems since the code excerpt is actually part of a hundred something lines of code in a single subroutine that opens a series of workbooks, each with their own password (and some without passwords). If "abcd1234" is passed everytime I open a workbook, there will be a series of password failures on the other workbooks.

    Is there an easy way to just pass the password along with the Workbooks_Open command?

    M

  4. #4
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default Re: Pass A Password To A Protected Workbook

    it's a "Private" Sub
    ==> only the workbook itself will be affected
    in fact here I didn't change your code, but in general we would prefere
    "Thisworkbook" or even more simple "Me"

    regards,
    Erik

  5. #5
    Board Regular
    Join Date
    May 2002
    Location
    San Diego
    Posts
    154

    Default Re: Pass A Password To A Protected Workbook

    So are you saying that I put the Private Sub in the workbook being opened as opposed to the workbook that has the VBA code that is calling the workbook?

    If so, then won't it run anytime someone opens the password protected workbook from Windows Explorer, for example? Seems to me that would defeat the purpose of password protecting the workbook. The only time we want someone to be able to open this workbook is when the VBA code opens the workbook and passes the password to unlock it.

    If I'm still not understanding, I do appreciate your patience in explaining this to me!

    M

  6. #6
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Your are right, with the password in the open event you might as well just never protect it.

    Try

    Workbooks.Open Filename:="myworkbook.xls"
    Workbooks("myworkbook").Unprotect Password:="abcd1234"

  7. #7
    Board Regular
    Join Date
    May 2002
    Location
    San Diego
    Posts
    154

    Default Re: Pass A Password To A Protected Workbook

    Thanks, Jacob.

    Unfortunately, it's the WORKBOOKS.OPEN command that seemed to trigger the password dialog box. I tried reversing the order of the commands you suggested but that just gave me an error message.

    Other ideas?

    M

  8. #8
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default Re: Pass A Password To A Protected Workbook

    yes, in the workbook being opened
    I didn't ask you why you would do that to my thoughts it was to force the users to open only with macros enabled ...
    So this is not an option for you.

    the code that you posted initially works fine for me
    Workbooks.Open Filename:="myworkbook.xls"
    ActiveWorkbook.Unprotect Password:="abcd1234"
    as a matter of fact you need the expression "ActiveWorkbook"
    since the newly opened workbook becomes the activeworkbook, it should work

    Do you get an error ?

  9. #9
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    26,862

    Default Re: Pass A Password To A Protected Workbook

    See how this works for you:

    Sub OpenMe()
    ****'** Open password protected files
    ****Application.Workbooks.Open _
    ****"C:\Path\FileName.xls", _
    ****False, False, , "password"
    End Sub

    '** Where the structure is as follows:

    '** Application.Workbooks.Open([Filename As String], [Update Links],
    '** [Read Only], [Format], [Password], [WriteResPassword],
    '** [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable],
    '** [Notify], [Converter], [AddtoMRU]) As Workbook


    Hope that helps,

    Smitty

  10. #10
    Board Regular
    Join Date
    May 2002
    Location
    San Diego
    Posts
    154

    Default Re: Pass A Password To A Protected Workbook

    Hey Erik;

    Sorry I threw you off. I certainly didn't mean to!

    If I step through the code in the debugger, the password dialog box pops up as soon as the Workbooks.Open Filename:="myworkbook.xls" command line is triggered.

    It then sits there until I enter the password and click OK in the dialog box.

    It then goes on to the ActiveWorkbook.Unprotect Password:="abcd1234" command line. Which, at this point, doesn't do anything since the workbook is already opened.

    So the question is: How do I open the workbook and pass it the password so I can get to the data in it and bypass the dialog box?

    Thanks again!

    M

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com