Pass A Password To A Protected Workbook

[markkramer]

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

 

[erik.van.geit]

Markkramer,
you can put this in the workbookmodule

Private Sub Workbook_Open()
ActiveWorkbook.Unprotect Password:="abcd1234"
End Sub

It would be good to add this too

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Protect Password:="abcd1234"
End Sub

regards,
Erik

 

[markkramer]

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

 

[erik.van.geit]

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

 

[markkramer]

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

 

[DRJ]

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"

 

[markkramer]

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

 

[erik.van.geit]

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 ?

 

[Smitty]

See how this works for you:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> OpenMe()
    <SPAN style="color:#007F00">'   Open password protected files</SPAN>
    Application.Workbooks.Open _
    "C:\Path\FileName.xls", _
    <SPAN style="color:#00007F">False</SPAN>, <SPAN style="color:#00007F">False</SPAN>, , "password"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">'   Where the structure is as follows:</SPAN>

<SPAN style="color:#007F00">'   Application.Workbooks.Open([Filename As String], [Update Links],</SPAN>
<SPAN style="color:#007F00">'   [Read Only], [Format], [Password], [WriteResPassword],</SPAN>
<SPAN style="color:#007F00">'   [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable],</SPAN>
<SPAN style="color:#007F00">'   [Notify], [Converter], [AddtoMRU]) As Workbook</SPAN>
</FONT>

Hope that helps,

Smitty

 

[markkramer]

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