Removing Password using VBA

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

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

NickHK

"JON JON" <[email protected]> wrote in message
news:#[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

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

"NickHK" <[email protected]> wrote in message
news:[email protected]...
> Jon,
> In the SaveAs, set the password to "".
>
> NickHK
>
> "JON JON" <[email protected]> wrote in message
> news:#[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>