Test to see if Workbook requires a password

[ExcelMonkey]

How do you test to see if a workbook requires a passord prior to opening?

Thanks

EM

 

[Ron de Bruin]

You can use a on error

Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(......................)
On Error GoTo 0

If Not mybook Is Nothing Then

 

[Gary Brown]

sub macro1
Dim strFileName As String
On Error Resume Next
strFileName = "C:\Temp\MyFile.xls"
Workbooks.Open Filename:=strFileName, Password:=""
If Err.Number = 1004 Then
MsgBox "File did not open." & vbCr & _
"Possible causes:" & vbCr & _
" - File does not exist." & vbCr & _
" - Password didn't work"
End If
end sub

 

[ExcelMonkey]

Ron when I do this I am still prompted with the password dialog box. I do
not want to be prompted with this. How do I get arround this?

Thanks

EM

 

[ExcelMonkey]

My code stops informing me of the incorrect password. The Error Handling
does not seem to progress to the If stmt.

Thanks

EM

 

[ExcelMonkey]

Is the Error Handling failing because I am using a Method after the Set stmt?

Set mybook = Workbooks.Open(...)

I cannot get On Error Goto to work either. The code always stops on the Set
stmt and prompts me with an error.

Thanks

EM

 

[Dave Peterson]

I think you changed the code.

This minor alteration of Ron's code worked fine for me:

Dim myBook As Workbook

Set myBook = Nothing
On Error Resume Next
Set myBook = Workbooks.Open(Filename:="c:\book1.xls", Password:="a")
On Error GoTo 0

If myBook Is Nothing Then
MsgBox "Not opened"
Else
MsgBox "Opened"
End If

 

[Dave Peterson]

When you change the code, you should post the current version in your followup.

Gary's code worked fine for me.

 

[ExcelMonkey]

I am using the same code and it still does not work. My code stops on the
Set stmt and prompts me with a VBA error dialog box. Could it be that I have
a setting in VBA set? I am doing this in my Personal.XLS workbook. I tried
it in a module in a regular xls file and I still get the same error. When I
type the password in corrrectly the code works fine. I am in Excel 2003/SP3
using XP.

The code is below.

Sub OpenFile()
Dim myBook As Workbook

Set myBook = Nothing
On Error Resume Next
Set myBook = Workbooks.Open(Filename:="C:\Documents and Settings\Our
Account\My Documents\Test File.xls", Password:="a")
On Error GoTo 0

If myBook Is Nothing Then
MsgBox ("Password was not correct.")
Else
MsgBox ("Password was correct.")
End If

End Sub

 

[Dave Peterson]

Try checking/changing a setting:
Inside the VBE
tools|options|general tab
Check "break in class module"

I'm betting you have "break on all errors" checked.

 

[ExcelMonkey]

Yup that did it. 2 quick questions:

1) How do you change those VBE settings via VBA code?
2) If were doing this in VB.Net, would this be an issue?

Thanks

EM

 

[Dave Peterson]

#1. I don't think you can. But I'll wait for someone to chime in with a
definitive answer.

#2. It could be.

Yup that did it. 2 quick questions:

1) How do you change those VBE settings via VBA code?
2) If were doing this in VB.Net, would this be an issue?

Thanks

EM