Solved

Remove Excel Workbook Password Programatically

Posted on 2008-06-19
2
418 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
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 = ""
    ~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
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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 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

15 Experts available now in Live!

Get 1:1 Help Now