Remove Excel Workbook Password Programatically

I have code which protects a workbook with a password so it cannot be opened without which works great. Now I am trying to reverse the process but when I save the workbook again with no password argument it just keeps the password on. I tried using a blank string and got an error.

How can I remove the password of a workbook?


'This function unprotects the specified sheet
Private Sub UnprotectSheet()
Dim excelApplication As Excel.Application
Dim workbook As Excel.workbook
Dim strTemporaryPath As String
    
    strFileName = "hi"
    strPath = "C:\Documents and Settings\bejhan.jetha\Desktop"
    strPassword = "pass"
    
    strTemporaryPath = "C:\Documents and Settings\bejhan.jetha\Desktop\Temp\"
    
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
 
    'Open the excel spreadsheet
    Set excelApplication = New Excel.Application
    Set workbook = excelApplication.Workbooks.Open(strPath & strFileName & ".xls", , , , strPassword)
    
    'Resave it unprotected
    workbook.SaveAs strTemporaryPath & strFileName
    
    workbook.Close
    Set workbook = Nothing
    
    excelApplication.Quit
    Set excelApplication = Nothing
    
    'Delete the original
    Kill strPath & strFileName & ".xls"
    
    'Copy the sheet back to the original directory
    FileCopy strTemporaryPath & strFileName & ".xls", strPath & strFileName & ".xls"
    
    'Delete the temporary
    Kill strTemporaryPath & strFileName & ".xls"
End Sub

Open in new window

LVL 1
bejhanAsked:
Who is Participating?
 
bandrieseConnect With a Mentor Commented:
I see no one has answered this question, so I give it a try.

You might have tried this already, but try setting the password to nothing. As in

strPassword = ""
    ~or~
strPassword = Null

I don't see anything in the code that makes the password go away. There may be a function or property for this without using the suggestion above.  I suspect that what's happening is once the password has been set it will remain there unless specificaly removed.
0
 
bejhanAuthor Commented:
Hmm, I thought I tried using a blank string and got an error. Must have put it as the wrong argument. I guess that's what happens when you stare at the screen for too long :P. Just for the record Null doesn't do anything. The blank string did the trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.