one way:

Public Sub OpenFileWithCyclicPassword()
Const FNAME As String = "D:\Manager\Inventory.xls"
Dim i As Integer
Dim wBook As Workbook
Dim pwords As Variant

Application.ScreenUpdating = False
pwords = Array("applepie", "orangejuice", "bananasplit", _
"icysundae", "cheezebun")
On Error Resume Next
For i = 0 To UBound(pwords)
Set wBook = Workbooks.Open(FNAME, _
ReadOnly:=True, password:=pwords(i))
If Not wBook Is Nothing Then Exit For
Next i
On Error GoTo 0
ThisWorkbook.Sheets("Sheet1").Range("B6").Value = _
wBook.Sheets("Sheet1").Range("Q3000").Value
wBook.Close savechanges:=False
Application.ScreenUpdating = True
End Sub


In article ,
"Edmund Seet" wrote:

"D:\Manager\Inventory.xls" is a Read-Only file which is
protected by either one of these 5 passwords (applepie,
orangejuice, bananasplit, icysundae or cheezebun). My
manager will vary the password periodically but it will
always be either one of these 5.

My active workbook is named "Target.xls". I need a macro,
to be put in Target.xls, that will go
open "D:\Manager\Inventory.xls" and cycle through these 5
passwords, then copy cell Sheet1!Q3000 to be paste-value
back to my Target.xls cell B6.

I tried recording a macro but to no avail. I don't know
why it couldn't record as I only can get.....

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 8/30/2003 by Edmund Seet
'

'
Range("C6").Select
End Sub

Please help me to complete this macro.

TIA
Edmund Seet (VBA rookie)

Million thanks to J.E.McGimpsey.