Option Explicit

Private Const ModName As String = "mdlGetWorkbook"

Private Enum OpenWorkbookStatus
UnknownResult = 0
WorkbookOpened = 1
PasswordRequired = 2
PasswordIncorrect = 3
FileFormatInvalid = 4
UnexpectedError = 99
End Enum


Sub Test_()
Const ProcName As String = "Test_"
On Error GoTo ErrorHandler

Const file As String = "S:\Test\password.xlsm"
'/Const file As String = "S:\Test\No Password.xlsm"
'/Const file As String = "Money Markets XLA Analysis.pptx"
'/Const file As String = "not a workbook.xlsx"

Dim status As OpenWorkbookStatus
Dim wkb As Workbook

status = OpenWorkbook(file, wkb, "")

Select Case status
Case OpenWorkbookStatus.PasswordRequired
MsgBox "The selected workbook appears to be password protected." & vbCrLf & vbCrLf & _
"Please enter the workbook's passwword and try again.", vbInformation '/, AppName
GoTo ExitSub

Case OpenWorkbookStatus.PasswordIncorrect
MsgBox "Sorry, I couldn't open the file with the password provided." & vbCrLf & vbCrLf & _
"Remember, Excel passwords are cASE sENSITIVE.", vbInformation '/, AppName
GoTo ExitSub

Case OpenWorkbookStatus.FileFormatInvalid
MsgBox "Sorry, I couldn't open the selected workbook." & vbCrLf & vbCrLf & _
"Either it's not an Excel workbook or it may be corrupted.", vbInformation '/, AppName
GoTo ExitSub

Case OpenWorkbookStatus.UnexpectedError
MsgBox "Sorry, an error occured trying to open the selected workbook.", vbInformation '/, AppName
GoTo ExitSub
End Select

ExitSub:
Exit Sub

ErrorHandler:
'/Log.LogError ThisWorkbook, ModName, ProcName, Err.Number, Err.Description, Erl
Resume ExitSub

End Sub



Private Function OpenWorkbook(ByVal FileName As String, ByRef wkb As Workbook, _
Optional pwd As String = vbNullString) As OpenWorkbookStatus

Const ProcName As String = "GetWorkbook"
On Error GoTo ErrorHandler

Dim status As OpenWorkbookStatus

Set wkb = Workbooks.Open(FileName, False, True, , pwd)
status = WorkbookOpened

ExitFunction:
OpenWorkbook = status
Exit Function

ErrorHandler:
Select Case Err.Number
Case 1004
' The password you supplied is not correct.
' Verify that the CAPS LOCK key is off and be sure to use the correct capitalization.
If InStr(1, Err.Description, "password", vbTextCompare) > 0 Then
If Len(pwd) = 0 Then
' no password was provided
status = OpenWorkbookStatus.PasswordRequired
Else
' An incorrect password was provided
status = OpenWorkbookStatus.PasswordIncorrect
End If

ElseIf InStr(1, Err.Description, "file format is not valid", vbTextCompare) > 0 Then
' Invalid file format
status = OpenWorkbookStatus.FileFormatInvalid
Else
'/Log.LogError ThisWorkbook, ModName, ProcName, Err.Number, Err.Description, Erl
status = OpenWorkbookStatus.UnexpectedError
Debug.Print Err.Number, Err.Description
End If
Case Else
'/Log.LogError ThisWorkbook, ModName, ProcName, Err.Number, Err.Description, Erl
status = OpenWorkbookStatus.UnexpectedError
Debug.Print Err.Number, Err.Description
End Select

Resume ExitFunction

End Function