I recently helped an individual in an Access Forum who wanted to know how to open a password protected Excel workbook/spreadsheet. Although the question was Access specific, the code can easily be used in Word, PowerPoint,…

'---------------------------------------------------------------------------------------
' Procedure : OpenPwdXLS
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open a password protected Excel Workbook
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strWrkBk  : Full path and Filename of the Excel Workbook to open
' sPwd      : Password to unlock/open the Workbook in question
'
' Usage:
' ~~~~~~
' OpenPwdXLS "C:\Testing\book1.xls", "MyPassword"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Jun-11             Initial Release
'---------------------------------------------------------------------------------------
Function OpenPwdXLS(strWrkBk As String, sPwd As String)
'Use late binding so no reference libraries are required
On Error GoTo Error_Handler
    Dim xlApp       As Object
    Dim xlWrkBk     As Object

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
    
    If Err.Number <> 0 Then
        'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set xlApp = CreateObject("excel.application")
    Else
        On Error GoTo Error_Handler
    End If

    xlApp.Visible = True 'make excel visible to the user
    Set xlWrkBk = xlApp.Workbooks.Open(strWrkBk, , , , sPwd)
    
    '... the rest of your code goes here
    
Error_Handler_Exit:
   On Error Resume Next
   Set xlWrkBk = Nothing
   Set xlApp = Nothing
   Exit Function

Error_Handler:
   MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
          "Error Number: " & Err.Number & vbCrLf & _
          "Error Source: OpenPwdXLS" & vbCrLf & _
          "Error Description: " & Err.Description _
          , vbOKOnly + vbCritical, "An Error has Occurred!"
   Resume Error_Handler_Exit
End Function