Solved

Remove Excel Workbook Password Programatically

Posted on 2008-06-19
2
416 Views
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

    

    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

0
Comment
Question by:bejhan
2 Comments
 
LVL 3

Accepted Solution

by:
bandriese earned 125 total points
Comment Utility
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
 
LVL 1

Author Comment

by:bejhan
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now