Subscript Out of Range Error
 

I'm writing a "Reset Passwords" macro for our office manager to periodically
reset all of her protected sheets. After prompting the user for the folder
to operate on, I use the FoundFiles object for the files in the folder and
its subfolders.

To avoid triggering an error by attempting to open a file that has a
different password than the one supplied (hard-coded currently but eventually
user input), I'd like to check the current password. If it is a match, open
and reset it, otherwise, write the filename to an array to be displayed at
the end of the macro.

The Workbooks.Open line worked, but the test in the outermost If..End If
block fails with a "Subscript Out of Range" error. Can anyone tell me what
I'm doing wrong?

Thank you for all assistance.

j = 0
For i = 1 To fs.FoundFiles.Count

If Workbooks(fs.FoundFiles.Item(i)).Password = "password" Then
Workbooks.Open (fs.FoundFiles.Item(i)), , , , "MaryJane"
With ActiveWorkbook
.Password = "password"
.Save
.Close
End With
Else
' File has a different password
j = j + 1
astrCantOpen(j) = fs.FoundFiles.Item(i)
End If

Next i

Subscript Out of Range Error
 

Workbooks("somethinghere.xls")
expects that somethinghere.xls is already open.

I think something like this should work:

Dim wkbk As Workbook

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open _
(Filename:="C:\my documents\excel\book1.xls", Password:="Password")
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox "not opened"
Else
MsgBox "Either no password, or it matched--either way, do the work"
End If




Sprinks wrote:

I'm writing a "Reset Passwords" macro for our office manager to periodically
reset all of her protected sheets. After prompting the user for the folder
to operate on, I use the FoundFiles object for the files in the folder and
its subfolders.

To avoid triggering an error by attempting to open a file that has a
different password than the one supplied (hard-coded currently but eventually
user input), I'd like to check the current password. If it is a match, open
and reset it, otherwise, write the filename to an array to be displayed at
the end of the macro.

The Workbooks.Open line worked, but the test in the outermost If..End If
block fails with a "Subscript Out of Range" error. Can anyone tell me what
I'm doing wrong?

Thank you for all assistance.

j = 0
For i = 1 To fs.FoundFiles.Count

If Workbooks(fs.FoundFiles.Item(i)).Password = "password" Then
Workbooks.Open (fs.FoundFiles.Item(i)), , , , "MaryJane"
With ActiveWorkbook
.Password = "password"
.Save
.Close
End With
Else
' File has a different password
j = j + 1
astrCantOpen(j) = fs.FoundFiles.Item(i)
End If

Next i

--

Dave Peterson

Subscript Out of Range Error
 

Thanks, Dave. Works great.

Sprinks

"Dave Peterson" wrote:

Workbooks("somethinghere.xls")
expects that somethinghere.xls is already open.

I think something like this should work:

Dim wkbk As Workbook

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open _
(Filename:="C:\my documents\excel\book1.xls", Password:="Password")
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox "not opened"
Else
MsgBox "Either no password, or it matched--either way, do the work"
End If




Sprinks wrote:

I'm writing a "Reset Passwords" macro for our office manager to periodically
reset all of her protected sheets. After prompting the user for the folder
to operate on, I use the FoundFiles object for the files in the folder and
its subfolders.

To avoid triggering an error by attempting to open a file that has a
different password than the one supplied (hard-coded currently but eventually
user input), I'd like to check the current password. If it is a match, open
and reset it, otherwise, write the filename to an array to be displayed at
the end of the macro.

The Workbooks.Open line worked, but the test in the outermost If..End If
block fails with a "Subscript Out of Range" error. Can anyone tell me what
I'm doing wrong?

Thank you for all assistance.

j = 0
For i = 1 To fs.FoundFiles.Count

If Workbooks(fs.FoundFiles.Item(i)).Password = "password" Then
Workbooks.Open (fs.FoundFiles.Item(i)), , , , "MaryJane"
With ActiveWorkbook
.Password = "password"
.Save
.Close
End With
Else
' File has a different password
j = j + 1
astrCantOpen(j) = fs.FoundFiles.Item(i)
End If

Next i

--

Dave Peterson