Bypassing password protection in Visual Basic

[Matt]

I'm programming a tool for work that is comprised of several different
spreadsheets. There are about 15 individual spreadsheets on a networked
drive and a master spreadsheet that takes and compiles data from the other 15.

The issue I'm running into is that each of the 15 individual spreadsheets is
password protected, so that only the owner of that spreadsheet can access it.
I've created a macro on the master spreadsheet that will retreive the data
from each of the individual spreadsheets, but each time it attempts to pull
data, it asks for the password.

Is there a way to bypass the password in Visual Basic so that the master
spreadsheet can compile the data from each of the individual spreadsheets
without it asking for a password? Any help or suggestions would be
appreciated.

 

[Bob Phillips]

Unprotect the sheet in VBA before working on it, the protect it afterwards

HTH

Bob

 

[Matt]

To clarify, I'm talking about the password to open the worksheet. I'm NOT
talking about the password used to protect formatting (Tools -> Protection).
If you're referring to the same password, I'm not sure how to disable it in
VB as you described. If there's a way to do it, that's what I need help
with... or any alternative suggestions be great as well.

 

[OssieMac]

Hi Matt,

Perhaps we need to clarify what you refer to as a worksheet.

A worksheet is one of the tabs (worksheets) within a workbook containing a
collection of worksheets. A workbook is actually the file that is saved and
you open. I suspect that you mean a workbook; not a worksheet.

If the above is correct, do you have the passwords for the individual
workbooks? If not, you need to get them.

The following example code opens workbooks with passwords.

Sub OpenWithPassword()
Dim strpath As String
Dim strPathNfile As String
Dim wb As Workbook

'Options for setting path
strpath = ThisWorkbook.Path & "\"
'strpath = "C:\OssieMac\Documents\Excel\"

'Variable with full path and filename
strPathNfile = strpath & "Test workbook with password.xls"

'Open the workbook with password
Workbooks.Open Filename:=strPathNfile, _
Password:="mypassword"

'Open the workbook including WriteResPassword
'Workbooks.Open Filename:=strPathNfile, _
Password:="mypassword", WriteResPassword:="mypassword"

Set wb = ActiveWorkbook

End Sub


Following from a post by Tom Ogilvy MVP

Note that there are two types of password arguments:

Password Optional Variant. A string that contains the password required to
open a protected workbook. If this argument is omitted and the workbook
requires a password, the user is prompted for the password.

WriteResPassword Optional Variant. A string that contains the password
required to write to a write-reserved workbook. If this argument is omitted
and the workbook requires a password, the user will be prompted for the
password.

 

[Mike Middleton]

Matt -

I don't have an answer to your questions, but I'm following this thread to
learn about the issues.

It would help me understand if you would avoid the word "spreadsheets." An
Excel workbook file contains one or more worksheets. When you write "15
individual spreadsheets," it's not clear to me whether you are referring to
15 workbook files or to one workbook file containing 15 worksheets.

In Excel pre-2007 the Tools menu has a Protection item with two or more
sub-items: Protect Sheet and Protect Workbook, each with an optional
password.

- Mike
http://www.MikeMiddleton.com


To clarify, I'm talking about the password to open the worksheet. I'm NOT
talking about the password used to protect formatting (Tools -> Protection).
If you're referring to the same password, I'm not sure how to disable it in
VB as you described. If there's a way to do it, that's what I need help
with... or any alternative suggestions be great as well.

 

[Tim Williams]

Exactly how are you getting the data from the files ?

Tim

 

[Matt]

Yes, I mean workbooks. Thank you.

Each of the individual workbooks belongs to an employee that would use their
own password to prevent anyone else from accessing them on a public networked
drive. Because the employees are at liberty to create their own passwords
and change them, the workbook that compiles the data would need to be able to
access data from these other workbooks without having the password.

 

[Matt]

Yes, I meant workbooks - there are 15 (or so) individual files that need to
be accessed by an independent workbook - more of a supervisory tool used for
compliling the data in the other workbooks.

 

[Matt]

I'm using a set of macros that open the file using the code:

Workbooks.Open Filename:="[FileName.xls]"

And then copying over data from the file to the master workbook. When the
individual workbooks that I'm attempting to gather data from are password
protected, it forces whoever uses the master workbook to have all of the
passwords.

 

[OssieMac]

Hi Matt,

If it were that easy to bypass the password then there is little point in
having them. Having said that I know that hackers can get past them without
too much trouble.

However, the way to achieve your aim is to have your network administrator
create a directory for which you have full read/write permissions to it and
any sub directories/files. The administrator then creates individual
directories for each of the users to which they have inidvidual access and
cannot see each others files. There should then be no need for them to have
passwords on the excel file because they should only be able to see their own
file. This type of system has been extensively used for a long time.