Opening a password protected excel file from a command button in access

[ksminor]

Hi All,
I have three excel files (ActualHires.xls, ActualPromotions.xls and
ActualSeparation.xls). These are password protected files (with the
same password). They are linked to an access database and whenever the
files are opened, one must supply the password and click the 'Enable
automatic refresh button.' What I was wondering was if this could be
done in access with a command button. I have pasted some code below
that I found but now I'm getting an error.
This is the code:


Dim BookNames As Variant
Dim B As Long

BookNames = Array("O:\ExcelFiles\ActualHires.xls",
"O:\ExcelFiles\ActualPromotions.xls",
"O:\ExcelFiles\ActualSeparations.xls")


For B = LBound(BookNames) To UBound(BookNames)
WorkBooks.Open FileName:=BookNames(B), _
UpdateLinks:=3, Password:="*******"
WorkBooks(B).Close SaveChanges:=False
Next B


But when I click the button, I get this error:
Run-time error '9' Subscript out of range.

and this line is highlighted:
WorkBooks(B).Close SaveChanges:=False

Any help would be greatly appreciated.

Thanks.

 

[ksminor]

I am getting a Run time error 9 - Subscript out of range.

 

[ali]

Hi All,
I have three excel files (ActualHires.xls, ActualPromotions.xls and
ActualSeparation.xls). These are password protected files (with the
same password). They are linked to an access database and whenever the
files are opened, one must supply the password and click the 'Enable
automatic refresh button.' What I was wondering was if this could be
done in access with a command button. I have pasted some code below
that I found but now I'm getting an error.
This is the code:


Dim BookNames As Variant
Dim B As Long

BookNames = Array("O:\ExcelFiles\ActualHires.xls",
"O:\ExcelFiles\ActualPromotions.xls",
"O:\ExcelFiles\ActualSeparations.xls")


For B = LBound(BookNames) To UBound(BookNames)
WorkBooks.Open FileName:=BookNames(B), _
UpdateLinks:=3, Password:="*******"
WorkBooks(B).Close SaveChanges:=False
Next B


But when I click the button, I get this error:
Run-time error '9' Subscript out of range.

and this line is highlighted:
WorkBooks(B).Close SaveChanges:=False

Any help would be greatly appreciated.

Thanks.