Test to see if Workbook requires a password

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

Thanks

EM

Ron
Tue Jul 22 15:54:53 CDT 2008

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> How do you test to see if a workbook requires a passord prior to opening?
>
> Thanks
>
> EM

gary
Tue Jul 22 15:57:06 CDT 2008

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
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"ExcelMonkey" wrote:

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

ExcelMonkey
Tue Jul 22 16:11:11 CDT 2008

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

"Ron de Bruin" wrote:

> 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
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> > How do you test to see if a workbook requires a passord prior to opening?
> >
> > Thanks
> >
> > EM
>

ExcelMonkey
Tue Jul 22 16:16:15 CDT 2008

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

Thanks

EM

"Gary Brown" wrote:

> 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
> --
> Hope this helps.
> Thanks in advance for your feedback.
> Gary Brown
>
>
> "ExcelMonkey" wrote:
>
> > How do you test to see if a workbook requires a passord prior to opening?
> >
> > Thanks
> >
> > EM

ExcelMonkey
Tue Jul 22 17:59:05 CDT 2008

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

"ExcelMonkey" wrote:

> 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
>
> "Ron de Bruin" wrote:
>
> > 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
> >
> >
> >
> > --
> >
> > Regards Ron de Bruin
> > http://www.rondebruin.nl/tips.htm
> >
> >
> > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> > > How do you test to see if a workbook requires a passord prior to opening?
> > >
> > > Thanks
> > >
> > > EM
> >

Dave
Tue Jul 22 19:39:54 CDT 2008

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

ExcelMonkey wrote:
>
> 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
>
> "ExcelMonkey" wrote:
>
> > 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
> >
> > "Ron de Bruin" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > --
> > >
> > > Regards Ron de Bruin
> > > http://www.rondebruin.nl/tips.htm
> > >
> > >
> > > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> > > > How do you test to see if a workbook requires a passord prior to opening?
> > > >
> > > > Thanks
> > > >
> > > > EM
> > >

--

Dave Peterson

Dave
Tue Jul 22 19:41:18 CDT 2008

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

Gary's code worked fine for me.

ExcelMonkey wrote:
>
> My code stops informing me of the incorrect password. The Error Handling
> does not seem to progress to the If stmt.
>
> Thanks
>
> EM
>
> "Gary Brown" wrote:
>
> > 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
> > --
> > Hope this helps.
> > Thanks in advance for your feedback.
> > Gary Brown
> >
> >
> > "ExcelMonkey" wrote:
> >
> > > How do you test to see if a workbook requires a passord prior to opening?
> > >
> > > Thanks
> > >
> > > EM

--

Dave Peterson

ExcelMonkey
Wed Jul 23 14:51:02 CDT 2008

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" wrote:

> 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
>
> ExcelMonkey wrote:
> >
> > 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
> >
> > "ExcelMonkey" wrote:
> >
> > > 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
> > >
> > > "Ron de Bruin" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > > Regards Ron de Bruin
> > > > http://www.rondebruin.nl/tips.htm
> > > >
> > > >
> > > > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> > > > > How do you test to see if a workbook requires a passord prior to opening?
> > > > >
> > > > > Thanks
> > > > >
> > > > > EM
> > > >
>
> --
>
> Dave Peterson
>

Dave
Wed Jul 23 15:09:24 CDT 2008

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 wrote:
>
> 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" wrote:
>
> > 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
> >
> > ExcelMonkey wrote:
> > >
> > > 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
> > >
> > > "ExcelMonkey" wrote:
> > >
> > > > 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
> > > >
> > > > "Ron de Bruin" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Regards Ron de Bruin
> > > > > http://www.rondebruin.nl/tips.htm
> > > > >
> > > > >
> > > > > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> > > > > > How do you test to see if a workbook requires a passord prior to opening?
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > > EM
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

ExcelMonkey
Wed Jul 23 20:38:00 CDT 2008

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" wrote:

> 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 wrote:
> >
> > 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" wrote:
> >
> > > 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
> > >
> > > ExcelMonkey wrote:
> > > >
> > > > 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
> > > >
> > > > "ExcelMonkey" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > "Ron de Bruin" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Regards Ron de Bruin
> > > > > > http://www.rondebruin.nl/tips.htm
> > > > > >
> > > > > >
> > > > > > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> > > > > > > How do you test to see if a workbook requires a passord prior to opening?
> > > > > > >
> > > > > > > Thanks
> > > > > > >
> > > > > > > EM
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Dave
Wed Jul 23 21:16:15 CDT 2008

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

#2. It could be.

ExcelMonkey wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > > ExcelMonkey wrote:
> > > > >
> > > > > 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
> > > > >
> > > > > "ExcelMonkey" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > > "Ron de Bruin" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Regards Ron de Bruin
> > > > > > > http://www.rondebruin.nl/tips.htm
> > > > > > >
> > > > > > >
> > > > > > > "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message news:793DC95C-A19E-480F-B4A4-0230A74D2C67@microsoft.com...
> > > > > > > > How do you test to see if a workbook requires a passord prior to opening?
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > > EM
> > > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson