Unprotect a workbook via macro?

[userpoets]

Good afternoon everyone, I have ten separate spreadsheets that I need to collate into one main tracking file. The issue I'm running into is that each of the individual files is read-write protected, specifically that a password needs to be entered to even view the file.

Right now when I open my main tracking file, to get the updated information I have to hit "enable content" and then manually type in ten passwords (really the same password ten times), and I was trying to figure a macro that would do all of this automatically for me, but I'm really not sure where to begin. My previous Excel endeavors have always involved unlocking an individual sheet or opening edit access, and this concept really has me stumped.

Any help is appreciated!

 

[OaklandJim]

It is not totally clear what you have. Are there ten separate worksheets in one workbook (file) or ten workbooks (files) containing one worksheet each?

Assuming ten workbooks (files) with one worksheet the simple answer is that you need a macro in the "main tracking file" (workbook) that opens a protected workbook and then unprotects the respective workbook. Once opened this is the code needed to unprotect with the password looks something like Workbooks("myworkbook").Unprotect "password"

I'll try to help if the task is clearer.

 

[userpoets]

I apologize for the absence, and I appreciate your response.

There are ten separate worksheets that are feeding information to one master sheet. All sheets are confidential to anyone but myself and my supervisor, so they are protected both from opening and editing.

I was hoping I could use something similar to this, which I have used to open read-only sheets in edit mode, update their data, then save and close:

Workbooks.Open "='C:\Users\userpoets\Desktop\Audits\Userpoets.xlsx", WriteResPassword:="unprotect", ReadOnly:=False, UpdateLinks:=True

However, this particular line of macro will open the document but will not auto-enter the password for me like I'm trying to do.