Removing Password using VBA

[JON JON]

Dear NG,

Please help me. I am trying to remove the "password to open a workbook"
using the code below. Although, it runs well during execution and does not
show any error messages, when I try to manually open my workbooks, it still
ask for password.

TIA

Jon-jon

Sub RemoveOpenPassword(myFilepath As String, myFilename As String, _
myOpenPassword As String, myWritePassword As String)
Dim Wb As Workbook
Set Wb = Workbooks.Open(Filename:=myFilepath & "\" & myFilename,
Password:=myOpenPassword, _
WriteResPassword:=myWritePassword, AddToMru:=False)
With Wb
.Password = ""
.Close True
End With
Set Wb = Nothing
End Sub

 

[NickHK]

Jon,
Excel2K anyway does not have a .Password property, so I can't test it.
Try SavingAs the WB without a password.

NickHK

 

[JON JON]

Thank you for the reply!

I have tried your suggestion and still does not work. BTW, I'm using XP.

This is my revised code

Sub RemoveOpenPassword(myFilepath As String, myFilename As String, _
myOpenPassword As String, myWritePassword As String)
Dim Wb As Workbook
Set Wb = Workbooks.Open(Filename:=myFilepath & "\" & myFilename,
Password:=myOpenPassword, _
WriteResPassword:=myWritePassword, AddToMru:=False)
With Wb
Application.DisplayAlerts = False
.SaveAs Filename:=.FullName, FileFormat:=xlNormal,
WriteResPassword:=myWritePassword, AddToMru:=False
Application.DisplayAlerts = True
.Close True
End With
Set Wb = Nothing
End Sub

 

[NickHK]

Jon,
In the SaveAs, set the password to "".

NickHK

 

[JON JON]

I don't know how the unprotect command affect the open password but it does
seem the cause of my problem. Below is the final code. Also, I should
add that the Save command is also needed befire resetting the protection.

Nick, I really appreciate how you had tried to help.

Till next time

Regards,

Jon-jon


Sub RemoveOpenPassword(myFilepath As String, myFilename As String, _
myOpenPassword As String, myWritePassword As String)
Dim Wb As Workbook
Set Wb = Workbooks.Open(Filename:=myFilepath & "\" & myFilename,
Password:=myOpenPassword, _
WriteResPassword:=myWritePassword, AddToMru:=False)
With Wb
.Unprotect "myPassword"
.Password = ""
.Save
.Protect "myPassword"
.Close True
End With
Set Wb = Nothing
End Sub