Remove Excel Workbook Password Programatically

Posted on 2008-06-19
Medium Priority
Last Modified: 2010-08-05
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
    Set workbook = Nothing
    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

Question by:bejhan

Accepted Solution

bandriese earned 500 total points
ID: 21827736
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 = ""
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.

Author Comment

ID: 21831742
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.

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Implementing simple internal controls in the Microsoft Access application.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question