Updating VBA Modules Programatically

[BobHunter]

Is there an easy way to update VBA modules programatically, like a patch ?

I have around 20 replicated spreadsheets, all with 1 protected VBA module under the bonnet. Rather than sending out a new template, or opening each spreadsheet and manually deleting and updating the module, can I do this throught the use of VBA code ?

Any help very much appreciated.

 

[RobBroekhuis]

You can. I'm not sure if for 20 instances it will save you much time, but you can. Are the coding changes in regular VBA modules, or in object code modules?
I think the easiest way to do this would be to copy the module(s) that need(s) to be replaced to a new workbook, write a separate module in that workbook to open the books to be changed, delete the code in the modules that need to be updated, and copy the updated code back in. Fairly straightforward. A simple example below:

Sub updatebook()
Dim s As String
With ThisWorkbook.VBProject.VBComponents("module1&quot.CodeModule
s = .Lines(1, .CountOfLines)
End With
Workbooks.Open "wrong.xls"
With ActiveWorkbook.VBProject.VBComponents("module1&quot.CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString s
End With
ActiveWorkbook.Save
activeworkbook.close
End Sub

Rob

 

[BobHunter]

Looks perfect for what I need but what is the code to unprotect the module before I delete from it ? i.e. I've locked the project for viewing.

Thanks in advance.....

 

[RobBroekhuis]

Hmmm... There doesn't appear to be a programmatic way of locking/unlocking the project, does there? In that case, you may have to rebuild the whole workbook, by copying the worksheets to a new workbook, importing programmatically the code modules, and saving. Still, I don't know how you'd lock the project after doing all that.
Rob

 

[BobHunter]

Found this, and it works ! What a messy way though !

Sub TestProtect()
Workbooks.Add.SaveAs "C:\Temp\Test.xls"
ProtectVBProject Workbooks("Test.xls&quot, "Jack"
Workbooks("Book1.xls&quot.Close True
End Sub

Sub TestUnprotect()
Workbooks.Open "C:\Temp\Test.xls"
UnprotectVBProject Workbooks("Test.xls&quot, "Jack"
End Sub

Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
Dim vbProj As Object

Set vbProj = WB.VBProject

'can't do it if already unlocked!
If vbProj.Protection <> 1 Then Exit Sub

Set Application.VBE.ActiveVBProject = vbProj

' now use lovely SendKeys to quote the project password
SendKeys Password & &quot;~~&quot;
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

 

[RobBroekhuis]

That's kinda cool. What is the control with ID=2578?
Rob

 

[BobHunter]

Absolutely no idea Rob ! I found this piece of code in Google Groups after an hour of searching. It is attributable to Bill Manville, who seems to be about in microsoft.public.excel.programming & microsoft.public.office.developer.vba a lot.

 

[BobHunter]

Aaaarrrgh, they do not work in conjunction with each other - any further ideas ??

If I run TestUnprotect alone, the result is an open, unprotected Resource_Plan.xls.

If I run TestUnprotect from within another sub, it prompts me for the password.

Sub Change_Module()
Dim s As String

With ThisWorkbook.VBProject.VBComponents(&quot;Updated&quot.CodeModule
s = .Lines(1, .CountOfLines)
End With

Call TestUnprotect

With ActiveWorkbook.VBProject.VBComponents(&quot;ModuleToReplace&quot.CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString s
End With

ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

Sub TestUnprotect()
Workbooks.Open &quot;Resource_Plan.xls&quot;
WorkbookToPatch = ActiveWorkbook.Name
UnprotectVBProject Workbooks(WorkbookToPatch), &quot;b0b&quot;
End Sub

 

[BobHunter]

A quick DoEvents has solved my problem....

Sub TestUnprotect()
Workbooks.Open &quot;Resource_Plan.xls&quot;
WorkbookToPatch = ActiveWorkbook.Name
UnprotectVBProject Workbooks(WorkbookToPatch), &quot;b0b&quot;
DoEvents
End Sub

Thanks anyway.