Automate response to protected files
 

I'm attempting (via automation from .Net) to scroll through a list of Excel
files and do something (which is immaterial to this question) with those
files.

That is working - until I get to a file which is password protected where
when I try to open the file it pops up the password box and stops my code.
Is there a property of the workbook that I can grab that says "this is
protected" so that I can skip that file?

First, I don't know anything about .Net.

But excel is pretty interesting.

If you try to open a non-protected workbook via code, it won't care if you try
with a bad password. If the workbook is protected, the workbook won't open --
but you won't get prompted.

For instance:

Option Explicit
Sub testme()
Dim wkbk As Workbook
Dim myList As Variant
Dim bCtr As Long
Dim myPath As String

myPath = "C:\my documents\excel\"
myList = Array("book2.xls", "book1.xls", "book3.xls")

For bCtr = LBound(myList) To UBound(myList)
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=myPath & myList(bCtr), _
ReadOnly:=True, Password:="xxx")
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox "either missing or wrong password: " & myPath & myList(bCtr)
Else
wkbk.Close savechanges:=False
End If
Next bCtr

End Sub

I could even use: password:=""
in that .open statement if I were afraid of guessing the correct password.


Rob Oldfield wrote:

I'm attempting (via automation from .Net) to scroll through a list of Excel
files and do something (which is immaterial to this question) with those
files.

That is working - until I get to a file which is password protected where
when I try to open the file it pops up the password box and stops my code.
Is there a property of the workbook that I can grab that says "this is
protected" so that I can skip that file?

--

Dave Peterson

Perfect. I just feed the line of code I'm using to open a file a password
and it just comes up with an error - which I can catch and cope with. I
guess it would be the same for automation from anything.

Many thanks.


"Dave Peterson" wrote in message
...
First, I don't know anything about .Net.

But excel is pretty interesting.

If you try to open a non-protected workbook via code, it won't care if you
try
with a bad password. If the workbook is protected, the workbook won't
open --
but you won't get prompted.

For instance:

Option Explicit
Sub testme()
Dim wkbk As Workbook
Dim myList As Variant
Dim bCtr As Long
Dim myPath As String

myPath = "C:\my documents\excel\"
myList = Array("book2.xls", "book1.xls", "book3.xls")

For bCtr = LBound(myList) To UBound(myList)
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=myPath & myList(bCtr), _
ReadOnly:=True, Password:="xxx")
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox "either missing or wrong password: " & myPath &
myList(bCtr)
Else
wkbk.Close savechanges:=False
End If
Next bCtr

End Sub

I could even use: password:=""
in that .open statement if I were afraid of guessing the correct password.


Rob Oldfield wrote:

I'm attempting (via automation from .Net) to scroll through a list of
Excel
files and do something (which is immaterial to this question) with those
files.

That is working - until I get to a file which is password protected
where
when I try to open the file it pops up the password box and stops my
code.
Is there a property of the workbook that I can grab that says "this is
protected" so that I can skip that file?

--

Dave Peterson