MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Opening a password protected Excel sheet using VB code

Posted by Varun Pant on November 20, 2001 9:49 PM

I want to open a password protected excel sheet using VB code, and have been successful in opening the worksheet, but it still prompts for the password everytime I want to open it. Is there a way to automate this with code, so that the password is hard coded in the VB program, and does not prompt for the password. I mean something like sendkey method, but it seem that it is not supported in excel. Thanks in advance.

Posted by Varun Pant on November 20, 2001 10:10 PM

Dear Friends,
Sorry for any inconvenience- but I have discovered the solution to my own problem.
Best regards,

Posted by Varun Pant on November 21, 2001 8:15 PM

The Solution

I created an instance of the Excel.Application class and named it AppExcel.
Dim AppExcel As Excel.Application
Dim cDir As String
cDir = App.Path + "\File_Name.xls"
' Checking if an existing excel sheet is open
Set AppExcel = GetObject("Excel.Application")
If AppExcel Is Nothing Then
Set AppExcel = CreateObject("Excel.Application")
If AppExcel Is Nothing Then
MsgBox " Could not start Excel", vbCritical, "Warning"
Exit Function
End If
AppExcel.Visible = True
MsgBox " A new instance of Excel created successfully"
MsgBox " An instance of Excel already open"
End If
' To specify the path App.path is the path to current directrory
AppExcel.Workbooks.Open cDir, , , , "Password"

etc etc etc.
Be sure to turn the excel warning off before using the code, and turn it back on again before exiting.
And yes, one more thing, just write the following bit of code before you exit!
Set AppExcel = Nothing
Hope that helps.