password protected excel

[Rachmat Hartono]

Hi,
How can I detect if an xls document is password protected prior opening it?
I wrote an application that opens xls files automatically, but it will stop
if a password protected document is opened. Any help would be greatly
appreciated.

Thanks,
Rachmat Hartono

[Rob Bovey]

Hi Rachmat,

There really isn't any direct way to determine if the file is protected,
you just have to try to open it and then check to see it you succeeded.
Here's an example:

Sub OpenFile()

Dim wkbBook As Workbook

Application.DisplayAlerts = False

On Error Resume Next
''' First line only required when doing this in a loop.
Set wkbBook = Nothing
Set wkbBook = Workbooks.Open("C:\MyBook.xls")
On Error GoTo 0

If wkbBook Is Nothing Then
MsgBox "Workbook was password protected."
Else
MsgBox "Workbook opened"
End If

Application.DisplayAlerts = True

End Sub

Note: other reasons besides password protection, the workbook not existing
at all, for instance, will cause this procedure to act as if the workbook
specified was password protected, so you have to take those things into
account before applying this method.

--
Rob Bovey, MCSE, MCSD
The Payne Consulting Group
http://www.payneconsulting.com

Rachmat Hartono wrote in message <#dPhqiFB$GA.261@cppssbbsa05>...

[Venkata Krishna]

Rachmat

 

Need some clarification.

 

The program will stop because the password needs to be provided for which it needs to prompt you. But after password is provided it will continue execution.

 

Do you want the program to skip the password protected files all together and not even stop for prompting for the password ? Or you mean that if you cancel the password prompt, it should continue ?

 

Regards

Venkata Krishna

 

Rachmat Hartono <rac...@bcl-computers.com> wrote in message news:#dPhqiFB$GA.261@cppssbbsa05...

[Venkata Krishna]

Rachmat

 

Since you  want to skip the password prompts (as per your Email), use the code as follows. If HasPassword.xls has the password, it is skipped and NoPassword.xls is opened (assuming it does not have password). This is done through password error handling and at the same time if the error is due to something else like incorrect path, you can add your handling code in the Else block of error handler. This code should be molded into your programme and let me know if you face any difficulty in doing so.

 

The bottom line is, to avoid password prompt, in  your statement/s to open the files, give a null password and handle the error.

 

Sub OpenFiles()
    On Error GoTo errorHandler
    Workbooks.Open FileName:="D:\HasPassword.xls", password:=""
    Workbooks.Open FileName:="D:\NoPassword.xls", password:=""
    MsgBox "Did not stop"   'Your statements
   

    Exit Sub
errorHandler:
    If InStr(1, Err.Description, "The password", vbTextCompare) <> 0 Then
        Resume Next
    Else
        MsgBox "Some other error"   'Your error handling here
    End If
End Sub

Regards

Venkata Krishna

Venkata Krishna <krish...@hotmail.com> wrote in message news:eqzp2fHB$GA....@cppssbbsa02.microsoft.com...

[Rachmat Hartono]

I will try that. Thank you so much for your help.

Best regards,
Rachmat Hartono

Venkata Krishna <krish...@hotmail.com> wrote in message

news:eAyC9oJB$GA.226@cppssbbsa03...