Error on trying to open passworded files

southernsun · March 25th, 2008, 07:16 AM

I have written a macro to process files emailed in to our office. The macro works well except when someone sends in a file which requires a password for it to open. Such files cause an error.
I am not interested in processing these passworded files as users should not be protecting their returns. I would rather simply ignore them.
Is there a way to test whether files are password protected against opening so that I can ignore these files?

BrianWren · March 25th, 2008, 09:52 AM

The approach should be to add an error handler that has code to handle this particular error.
You add to the routine that opens the file, at the top, On Error GoTo Er. (That "Er" is a label in the same routine. You can call the label whatever you like. I like Er...)

Then at the botom of your code, add an exit that will cause program flow to exit the routine prior to the label Er when there is no error. You can use this exit point when there is an error if you add a lable just before the exit point. (Again, whatever you like, but I like Rs for "resume" point.)

Then, after the label in the On Error statement, handle the error. Let's presume the error is 15. THen the code would be something like:

Code:

Public Sub Whatchamo()

    On Error GoTo Er

    ' Your code here that might raise the error.

Rs: ' Do any cleanup here, closing files, etc.
    Exit Sub

Er:
    If Err.Number = 15 Then
        MsgBox "The file " & <put FilNam here, so msg is informative> & " " & _
               "could not be opened.", vbCritical, "File Open Error"
    Else
        MsgBox "Error " & Err.Description, vbCritical, "Error"
    End If

    Resume Rs

Exit Sub

southernsun · March 25th, 2008, 10:32 AM

Thanks Brian.
I had considered using a general error handling technique but was worried that it might inappropriately handle unexpected errors that might crop up in future. Hence, I was looking for some sort of specific test for passworded files.
Your suggestion of specifically addressing the error number tackles this problem so it'll work great. Just wanted to say thanks...