VBA Help: Open Workbook with multiple possible passwords

N

Newbie176

New Member
Joined
Mar 15, 2009
Messages
3
:(I have a model I need to amend that opens workbooks and summarises data from a number of workbooks into it.

The workbooks are password protected and a change of passwords has meant a number of possible passwords are correct for any workbook so I need a way of cycling through to see which works and then open the workbook so it can be used by the model.

To open each workbook the model uses the following code:

Workbooks.Open Finename:=file.Path, Update Links:=0, ReadOnly:=True Password:=strPassword (variable that picks password from sheet)

Could you please help with how code could loop through atleast 4 passwords and open using the correct password?

Thanks for your help in advance:confused:
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi

You can loop thru the possibilities eg assuming you have an array myArray filled with your possible passwords then you can loop thru testing each:

Code: 
Dim i As long, wb As Workbook, bOpen As Boolean
On Error Resume Next  'turn error reporting off
For i = LBound(myArray) To UBound(myArray)
  Set wb = Workbooks.Open(filenamehere,Password:=myArray(i))
  If Not wb Is Nothing Then bOpen = True:Exit For
Next i
On Error Goto 0  'turn error reporting back on

If bOpen Then 
  MsgBox "File was opened with password: " & myArray(i)
Else
  MsgBox "Password not found for file!"
End If
 
Upvote 0
Hi Richard thanks for your quick reply

I have created an array (to my best ability) and attach the full code I now have below:

Sub Passwordmacro()
Dim myArray As Variant
myArray = Worksheets("Sheet1").Range("A3:A5")
Dim i As Long, wb As Workbook, bOpen As Boolean
On Error Resume Next 'turn error reporting off
For i = LBound(myArray) To UBound(myArray)
Set wb = Workbooks.Open("C:\Documents and Settings\Susan Thornton\My Documents\VBA\Test\Book3.xls", Password:=myArray(i))
If Not wb Is Nothing Then bOpen = True: Exit For
Next i
On Error GoTo 0 'turn error reporting back on
If bOpen Then
MsgBox "File was opened with password: " & myArray(i)
Else
MsgBox "Password not found for file!"
End If
End Sub

The password are in A3 to A5 of sheet1 and the correct password is in A5. When I run the code it give me the "Password not found for file" and does not open.

Could you help me with this please:(
 
Upvote 0
Hi Susan

There's nothing wrong with the way you've incorporated the code - the only thing is that by assigning a range reference to myArray you actually create a 2 dimensional array (albeit it one with three rows and only a single column). This hasn't caused you a problem however, altho it doesn't hurt to be explicit:

Code: 
Sub Passwordmacro()
Dim myArray As Variant
myArray = Worksheets("Sheet1").Range("A3:A5")
Dim i As Long, wb As Workbook, bOpen As Boolean
On Error Resume Next  'turn error reporting off
For i = LBound(myArray,1) To UBound(myArray,1)
  Set wb = Workbooks.Open("C:\Documents and Settings\Susan Thornton\My Documents\VBA\Test\Book3.xls", Password:=myArray(i,1))
  If Not wb Is Nothing Then bOpen = True: Exit For
Next i
On Error GoTo 0  'turn error reporting back on
If bOpen Then
  MsgBox "File was opened with password: " & myArray(i,1)
Else
  MsgBox "Password not found for file!"
End If
End Sub
If this doesn't work then it suggests that you don't actually have the correct password in A3:A5 - do you perhaps have a trailing space or a spelling error in the range? Passwords are case sensitive so that PassWord does not equal PASworD, so check for this too.
 
Upvote 0
Try

Code: 
Set wb = Workbooks.Open("C:\Documents and Settings\Susan Thornton\My Documents\VBA\Test\Book3.xls", Password:=myArray(i, 1))

because reading from a worksheet directly in the way that you have done actually creates a two-dimensional array.
 
Upvote 0
It works. Brilliant!!:)

You have been fab, I really never thought that I could get this fixed within 2hrs, most of that time being me!!

Thanks so much for your help.

Sue
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Password protect workbook when opened. On failed attempt close, send email and delete workbook.
2 3
Replies
20
Views
1K
camerong
C
A
  • Question
Merge/ combine password protected data from multiple folders
Replies
0
Views
515
Ani_Excel
A
P
  • Solved
Existing macro to password protect sheets has stopped working
Replies
2
Views
939
purplekombi
P
K
  • Solved
Enter "Read-Only" password when opening a workbook with VBA
Replies
2
Views
700
kgkev
K
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
363
glynn1969
G

Forum statistics

Threads
1,214,979
Messages
6,122,551
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top