PDA

View Full Version : Solved: Unlocking several VBAProjects



Gil
05-02-2010, 05:06 AM
Hello
I have several VBAProjects that I need to unlock for editing, in bulk it will be timesaving.
I was looking at this code suggested by macropod for unlocking several VBAProjects on the following post
http://www.vbaexpress.com/forum/showthread.php?t=30687&highlight=open+excel+vba+project+in+bulk



Option Explicit
Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub
When I apply it to a sample workbook and try to run it in VBA editor, it asks for a Macro name.
Forgive my ignorance but what are SendKeys
Do you substitute "~~" for "my pasword"
Also it has code VB,should it not be VBA

xld
05-02-2010, 05:40 AM
Worked fine for me.

VBA is VB, just within a host application.

mdmackillop
05-02-2010, 05:55 AM
Where a routine contains a parameter, it needs to be called by another routine which supplies that parameter


Option Explicit
Sub Test()
Call UnprotectVBProj("MyPassword")
End Sub

Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

Gil
05-02-2010, 11:48 AM
Hello xld
Sorry but your reply does not help 'Worked fine for me'. I put my hands up and surrender as a novice. If you do do not like me I will leave the forum, reluctantly.
Gil

Gil
05-02-2010, 11:50 AM
Hello mdmackillop
Thank you for your reply, I am trying to see where to fit your response in. If you could give me a prompt it will help me.
Gil

GTO
05-02-2010, 11:59 AM
Hi Gil,

Show us the code that you are calling this procedure with.

Mark

xld
05-02-2010, 01:52 PM
Hello xld
Sorry but your reply does not help 'Worked fine for me'. I put my hands up and surrender as a novice. If you do do not like me I will leave the forum, reluctantly.
Gil

That's a tad paranoid response is it not. I read your post, tried it, had no problem, so I relayed this to you.

Gil
05-02-2010, 03:54 PM
Hello xld
No paranoia. I thought my post had several questions that could be answered beginning with the first
'When I apply it to a sample workbook and try to run it in VBA editor, it asks for a Macro name'
I tried it in 2007 and that is the message it gave
Please give a step by step how you ran it.
Gil

Gil
05-02-2010, 03:59 PM
Hello GTO
Perhaps this is my problem. I am not sure what what you mean when you ask
'Show us the code that you are calling this procedure with' I thought the code I had posted was sufficient for the task, I just didn't understand how to use it.
Gil

xld
05-02-2010, 04:25 PM
Hello xld
No paranoia. I thought my post had several questions that could be answered beginning with the first
'When I apply it to a sample workbook and try to run it in VBA editor, it asks for a Macro name'
I tried it in 2007 and that is the message it gave
Please give a step by step how you ran it.
Gil

Gil, to be honest, I think Malcolm (mdmackillop) hit the nail on the head.

It worked for me because I created a small procedure to call your procedure, passing it the password as a parameter. I did not try to run your procedure directly as I now suspect (and as MD assumed) you had done.

Gil
05-02-2010, 05:44 PM
Thank you xld
Please do not misunderstand my intentions. I highly respect the support you & others offer in the forum. However just sometimes take a step back to when you first started on the vba experience. You quote 'as md assumed' and I admit I did not run the procedure but that is where my knowledge is lacking.All I am asking is for guidance on how to progress step by step.
Again many thanks for your patience.
Gil

xld
05-03-2010, 01:31 AM
Read MDs post again, I think (admittedly from my position of a lot more experience than you have) that he has explained it all. If you don't get it, respond with respect to what MD has said and we can see if we can take it from there.

Gil
05-03-2010, 04:57 AM
Hello mdmackillop
Thank you for the addition to the code. It works fine. It only unlocks the active workbook so I have been looking on the web to see if I can work out how to open several projects. I haven't found anything hence this post.
Your continuing support and help is greatly appreciated.
Gil

Gil
05-03-2010, 04:59 AM
Hello xld
Sorry. I have put all the toys back in my pram and look forward to your assistance in the future.
Gil

xld
05-03-2010, 06:29 AM
You need to go through all of the books in a loop, something like


Sub UnprotectThemAll()
Dim wb As Workbook

For Each wb In Application.Workbooks

Call UnprotectVBProj(wb, "myPassword")
Next wb
End Sub


Sub UnprotectVBProj(ByRef wb As Workbook, ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = wb.VBProject
If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub


I haven't tested this, it may need tweaking.

Gil
05-03-2010, 03:19 PM
Hello xld
I have been experimenting with your code and it still only wants to open one VBA Project at a time. I have now attached a winzip which contains a sheet marked OPEN ME and some Example workbooks. The password is "mypassword"
If you can help once again I would appreciate it.
Gil

Gil
05-06-2010, 05:17 AM
Hello
After several attempts to make any sense out of this I am still stuck. The example file in my last post runs and looks like it is opening the other VBA project files but only one opens. If you run it again it will open the next and so on.
Any help would be appreciated.
Gil

GTO
05-06-2010, 04:33 PM
No promises, but this seems to work.

Sub UnprotectThemAll()
Dim WB As Workbook

For Each WB In Application.Workbooks
If Not WB.Name = ThisWorkbook.Name Then Call UnprotectVBProj(WB, "mypassword")
Next WB
End Sub

Sub UnprotectVBProj(ByRef WB As Workbook, ByVal Pwd As String)
Dim vbProj As Object
Dim OpenWindow As Object ' As VBIDE.Window

For Each OpenWindow In Application.VBE.VBProjects.VBE.Windows
If InStr(1, OpenWindow.Caption, "(Code)") > 0 Then OpenWindow.Close
Next

DoEvents

Set vbProj = WB.VBProject

If vbProj.Protection <> 1 Then Exit Sub ' already unprotected

Set Application.VBE.ActiveVBProject = vbProj

DoEvents

SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

Hope that helps,

MArk

Gil
05-06-2010, 07:00 PM
Hello GTO

Yes GTO it does look promising. I have applied it to my example file and all 5 vba open ok, perfect just what I wanted. One observation is if I set the password for OPEN ME and run the code it opens the 5 but not the OPEN ME. Any thoughts.
Later I will apply it to my project and see what the results are and update the thread.
Many thanks GTO for the help on this one.
Gil

GTO
05-06-2010, 09:39 PM
...One observation is if I set the password for OPEN ME and run the code it opens the 5 but not the OPEN ME. Any thoughts....

Hi Gil,

Take out the IF test here:

For Each WB In Application.Workbooks
If Not WB.Name = ThisWorkbook.Name Then Call UnprotectVBProj(WB, "mypassword")
Next WB


Mark

Gil
05-07-2010, 06:26 AM
Hello GTO
Yes, definitely, it all works well for me and many thanks for your help. I have added an example for anyone seeking a similar answer.
Also I would like to thank mdmackillop & xld for their input to the completion of this part of my project.

GTO
05-07-2010, 07:14 AM
You are most welcome and I'm glad it's running smooth:beerchug:

xld
05-07-2010, 07:23 AM
Well done Mark, a nice piece of work :)

GTO
05-07-2010, 09:23 AM
Thank you; unfortunately :o: No credit here, just remembered there was something about closing windows and looked til I found Ivan F Moala's note in #2 at http://www.mrexcel.com/forum/showthread.php?p=101971

I don't think I had seen this before; Set Application.VBE.ActiveVBProject = vbProj

What exactly is it doing?

I have only seen examples of sendkeys, but I happened upon this:

http://www.ozgrid.com/forum/showthread.php?t=13006&page=2 (#20)

Since I do not know French (somedays I barely know English!), not sure if I'll figure it out...

Have a great weekend :hi:

Mark

GTO
05-09-2010, 03:02 AM
Not sure if bumping another fella's thread is okay, but reference:


...I don't think I had seen this before; Set Application.VBE.ActiveVBProject = vbProj

What exactly is it doing?...

I'm blaming sleep depravation; I think I have it. Am I correct in that the above get the keys sent to the correct project's properties dialog?

Thank you,

Mark

xld
05-09-2010, 03:31 AM
Yes, it settin g the activeproject.

GTO
05-09-2010, 03:55 AM
Yes, it settin g the activeproject.

Thanks Bob; I figured my head was screwed on a bit better tonight - just wanted confirmation. VBIDE code seems a bit trickier to me with not being able to step through and see stuff happen (though trying to was a bit comical)

Me: "Close the window"
Excel: "You 'tard, I'm opening the window back up; you are stepping thru code..."
Me: "...uhhhhh...."