How do I loop through VBAProjects

[MPW]

Hi All,

I am using Excel (Office 2003).
I am trying to unprotect a vba project using sendkeys.
The trick to using the sendkeys is to make sure the right object is selected.
For this reason I can't step through the code because it will move the focus.

Basically, I am using a browse window to select the workbook to modify. I then open the workbook. Next I needed to unprotect the VBProject. At first I was using

"%{F11}TE(password)~~", True

but this did not work on every system.
So I thought when I want to edit a project I just click on the project in the "Project Explorer" so I thought I would try

"%{F11}^R{TAB}{ENTER}(password){ENTER}", True

I changed the ~ for {ENTER} so I could read it easier years later when I have to look at it again.

Again the results were spotty because I have more then 1 workbook open.

At this point I decided to use the workbook name that I all ready had trapped to open the workbook in the first place. I have tried a lot of different combinations with out any real success. I am using stuff from Chip Pearson's site (http://www.cpearson.com/excel/vbe.aspx). It seems to be the best resource on this subject.

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

'I tried'
Workbooks(oldwrkbk.Name).VBProject.VBE.MainWindow.SetFocus
Workbooks(oldwrkbk.Name).VBProject.VBE.MainWindow.Select
'etc.'

I have several people using the Workbook I am trying to fix with this method.
They are in different countries all over the world so I can't easily fix them without making some kind of patch like this.

Please let me know if you have any insight on how to do it.

MPW

 

[GTO]

You can close the extranious windows in VBIDE first, then maybe a better shot at aiming SendKeys:

Sub UnprotectThemAll()
    Dim WB As Workbook
    
    For Each WB In Application.Workbooks
        If Not WB.Name = ThisWorkbook.Name Then Call UnprotectVBProj(WB, "mypassword")
        '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

 

[MPW]

Thanks for the Code GTO

I will have to try to get my brain around it but looks promising.
1. I thought I had to loop through the VBAProjects not the workbooks themselves.
2. I didn't know that you could use

Call UnprotectVBProj(WB, "mypassword")

to get the process started.
Everything I saw before was done purely through sendkeys.
I do not understand what the

For Each OpenWindow

does as well as Execute command at the bottom.
If you don't mind taking the time I would like to understand it a little more.

 

[theaudioguy]

I'm trying to figure out what this part means, but I can't decipher it.

If InStr(1, OpenWindow.Caption, "(Code)") > 0 Then OpenWindow.Close

 

[MPW]

I worked through most of the code and decided to go straight to

Set Application.VBE.ActiveVBProject = VBProj
SendKeys "pwd~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

It worked fine on my computer, but on a different system it brings up the VBAProject Properties dialog box.

I guess the 2nd ~ is not recognized.
As a result I have to click "OK" manually.

Any ideas?

 

[theaudioguy]

Have you found a solution yet, MPW?