Solved

Remove Excel Workbook Password Programatically

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro to import XML in Access 2013 2 34
Dcount using a date in a table compared to today's date 3 29
User Level Security 6 38
Create macro from runcode 30 24
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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

785 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