Solved Linking Password Protected Workbooks

December 21, 2012 at 08:38:38
Specs: Windows XP

I've seen it said that you cannot linked password protected workbooks but I'm hoping the person that posted that, just didn't know how to do it.

I have three workbooks used by different teams in the same department. The 'Master' workbook pulls back data a2 through r400 from WB 'Raw Data' and s2 through w400 from WB 'Review.'

All three of these workbooks are used by different teams (Quality for Master, Accounts for Raw Data, Review for Review) which is why they're password protected.

Is there anyway to NOT have to put in the password for the fields on Master to automatically update?


See More: Linking Password Protected Workbooks

Report •


#1
December 21, 2012 at 09:40:29

Let's think about this...

The file is Password protected so that only authorized individuals can see the contents.

If linking to unopened, password protected files was allowed, and I knew the name and location of the password protected file I could just enter a formula like the one below in A1 of each sheet and drag it down and over, thus creating a copy of the values in every cell of the password protected file:

='\\MyPath\Users\UserName\My Documents\[MyFile.xlsm]Sheet1'!A1

That would sort of defeat a major portion of the password protection, wouldn't it? You'd only be able to see the values in the protected sheet, not the formulas, but that still might defeat the main purpose of the protection.

However, if you are an authorized user of the files, and you know the passwords, you could use code similar to this to open the password protect files and then have the links update.

Sub Open_Protected_File()
  Workbooks.Open Filename:= _
     "\\MyPath\Users\UserName\My Documents\MyFile.xlsm", Password:="Secret"
End Sub

Since the code contains the password to the password protected file you should password protect the code and hide it so that unauthorized users can't access it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 21, 2012 at 10:28:13

Well, in this case viewing the data is fine for most employees, it's really changing the data that the protection is set up for.

All three WBs have a sheet in common called 'Data' and because there are so many users (80+), we had to create 3 workbooks. The 'Raw Data' WB is for the initial case information to be entered in cells A-R, rows starting at 2 and going down accordingly. The 'Review' WB is for the review team to look at what the Accounts team has done and add comments in cells S-W. The Master wb is for management to see what their two teams have done.

While I was explaining that, I realized that I had neglected to say that the Raw Data and Review WBs also update each other so all three wbs have to have the same information at the same time.

If I use that code above, would I have to include it in each workbook? And if I use it in Master, how would I include the two other wb passwords? I don't understand how to format the same request twice. Would it be two 'sub open' and 'end sub' or just one 'sub open' with the two addresses?


Report •

#3
December 21, 2012 at 14:21:07
✔ Best Answer

If viewing the data is OK, why are the workbooks protected?

Use protection at the worksheet level to prevent changes and you shouldn't have any problems linking to them.

If you must protect them at the workbook level, then you'll need to open all workbooks with code that resides in the workbook that is trying to pull data from them.

Sub Open_Protected_File()
  Workbooks.Open Filename:= _
     "\\MyPath\Users\UserName\My Documents\MyFile.xlsm", Password:="Secret"

  Workbooks.Open Filename:= _
     "\\MyPath\Users\UserName\My Documents\MyOtherFile.xlsm", Password:="Secret"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 21, 2012 at 14:38:19

Thanks, DerbyDad, that's what I needed to know.

And because the wbs have sensitive customer information, we have to protect them in case someone from another department worked their way through the elaborate line of folder-after-folder till they came to our wbs and decided the names were so interesting they just HAVE to open it up and see what it's all about. You know....bureaucratic nonsense.


Report •

#5
December 21, 2012 at 14:48:17

So the protection is primarily to prevent viewing, not primarily to prevent changing the data as you stated in Response #2.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 24, 2012 at 06:00:17

No, it's for both. The team members who are supposed to use the workbook can view but not change ALL fields, only their own. Other employees who are NOT team members can NOT even view the workbook because of sensitive data. Does that make sense?

Report •


Ask Question