Response of Access VBA when handing wrong password for import of encrypted password

Question

I am using this code for importing encrypted excel into access 2007:

xlApp.Workbooks.Open FileName:=dlgOpen.SelectedItems(i), PASSWORD:=strPasswd

It works nicely if I give the correct password, or nothing (I'll prompt user to enter the password again for they give a blank password), but not for wrong password, Access will prompt for error "1004", saying that the password is not correct, and ask for debug.

How can I change it so that it will reject user's entry and prompt for password again?

Thanks!

Answer 1

You would have to do some error trapping. Off the top of my head something like this

Public Sub Import (strPassword as String)
On error goto Error_trap
If len(strPassword)=0 then
    strPassword=InputBox(“Enter Password”)
End if
xlApp.Workbooks.Open FileName:=dlgOpen.SelectedItems(i), PASSWORD:=strPasswd

Exit Sub

Error_trap:

If err.Number=1004 then
    If msgbox(“No Password entered, do you want to try again?”,36)=vbYes then
        Import ""
    Else
        Msgbox “Ok fine be that way”
    End if
Else
    Msgbox “Error happened in sub Import, error description, “ & err.description
End if
End Sub