We are trying to push some new code to the our code modules in a workbook.
The issue we face right now is that the modules are protected. Is there a
way to programmatically remove the module protection. So that I can run the
bellow code so that I do not get an error accessing the protected modules.

Sub PatchWorkbook(PatchWBPath As String)
Dim ve As VBE
Dim xl As Excel.Application
Dim wb As Workbook
Dim codeModule As codeModule

Set xl = New Excel.Application
xl.EnableEvents = False
xl.DisplayAlerts = False

Set wb = xl.Workbooks.Open(PatchWBPath, False, False, , "", "", , , , ,
False)
wb.UpdateRemoteReferences = False
xl.Calculation = xlCalculationManual

Set ve = xl.VBE
Debug.Print ve.VBProjects(1).Protection

Set codeModule = ve.ActiveVBProject.VBComponents("ThisWorkbook")

wb.Save
wb.Close
Set wb = Nothing
Set xl = Nothing
End Sub

Thanks in advance!

Reg,
Adam

Adam wrote...
We are trying to push some new code to the our code modules in a
workbook.
The issue we face right now is that the modules are protected. Is
there a
way to programmatically remove the module protection. So that I can
run the
bellow code so that I do not get an error accessing the protected
modules.
....

Read the archived thread linked below.

http://groups-beta.google.com/group/...cf7803979b9815
(or http://makeashorterlink.com/?P5192144A )

Thanks Harlan, I wish I didn't have to go down the road of SendKeys...but...I
guess it is my only option.

"Harlan Grove" wrote:

Adam wrote...
We are trying to push some new code to the our code modules in a
workbook.
The issue we face right now is that the modules are protected. Is
there a
way to programmatically remove the module protection. So that I can
run the
bellow code so that I do not get an error accessing the protected
modules.
....

Read the archived thread linked below.

http://groups-beta.google.com/group/...cf7803979b9815
(or http://makeashorterlink.com/?P5192144A )

Rather than 'updating' workbooks, make your code into an add-in. Now,
all you have to do is replace the add-in with the new version.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
We are trying to push some new code to the our code modules in a workbook.
The issue we face right now is that the modules are protected. Is there a
way to programmatically remove the module protection. So that I can run the
bellow code so that I do not get an error accessing the protected modules.

Sub PatchWorkbook(PatchWBPath As String)
Dim ve As VBE
Dim xl As Excel.Application
Dim wb As Workbook
Dim codeModule As codeModule

Set xl = New Excel.Application
xl.EnableEvents = False
xl.DisplayAlerts = False

Set wb = xl.Workbooks.Open(PatchWBPath, False, False, , "", "", , , , ,
False)
wb.UpdateRemoteReferences = False
xl.Calculation = xlCalculationManual

Set ve = xl.VBE
Debug.Print ve.VBProjects(1).Protection

Set codeModule = ve.ActiveVBProject.VBComponents("ThisWorkbook")

wb.Save
wb.Close
Set wb = Nothing
Set xl = Nothing
End Sub

Thanks in advance!

Reg,
Adam

I need to add a macro to a distributed workbook and found your response. I
looked at your reference and tried using the following code, but it does not
work. I did check the MBVE reference mentioned by Tom Oglivy, but still no
job. Here's the code I'm using:

Sub TryToUnlock()
Dim WB As Workbook
Set WB = Workbooks("TestOpen.xls")
UnprotectVBProject WB
End Sub

Sub UnprotectVBProject(WB As Workbook) ', ByVal Password As String)

' Bill Manville, 29-Jan-2000
'
Dim VBP As VBProject, oWin As VBIDE.Window
Dim wbActive As Workbook
Dim i As Integer

Set VBP = WB.VBProject
Set wbActive = ActiveWorkbook

If VBP.Protection < vbext_pp_locked Then Exit Sub

Application.ScreenUpdating = False

' close any code windows to ensure we hit the right project
For Each oWin In VBP.VBE.Windows
If InStr(oWin.Caption, "(") 0 Then oWin.Close
Next oWin

WB.Activate
' now use lovely SendKeys to unprotect
Application.OnKey "%{F11}"
SendKeys "%{F11}%TE", True '& Password & "~~%{F11}", True
'If VBP.Protection = vbext_pp_locked Then
' failed - maybe wrong password
' SendKeys "%{F11}%TE", True
'End If

End Sub

"Harlan Grove" wrote:

Adam wrote...
We are trying to push some new code to the our code modules in a
workbook.
The issue we face right now is that the modules are protected. Is
there a
way to programmatically remove the module protection. So that I can
run the
bellow code so that I do not get an error accessing the protected
modules.
....

Read the archived thread linked below.

http://groups-beta.google.com/group/...cf7803979b9815
(or http://makeashorterlink.com/?P5192144A )

Hello all,

Here is my solution
http://www.ozgrid.com/forum/showthre...t=13006&page=2

You can download the '*JB Word components exporter.zip*' archive. It
contents the source code.

hf ;)


--
gg67
------------------------------------------------------------------------
gg67's Profile: http://www.excelforum.com/member.php...o&userid=33548
View this thread: http://www.excelforum.com/showthread...hreadid=336694

I am sure you will be very popular with people who make add-ins as part of
their living

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"gg67" wrote in message
...

Hello all,

Here is my solution
http://www.ozgrid.com/forum/showthre...t=13006&page=2

You can download the '*JB Word components exporter.zip*' archive. It
contents the source code.

hf ;)


--
gg67
------------------------------------------------------------------------
gg67's Profile:
http://www.excelforum.com/member.php...o&userid=33548
View this thread: http://www.excelforum.com/showthread...hreadid=336694

I don't see it...could you point me to the right place?

"gg67" wrote:


Hello all,

Here is my solution
http://www.ozgrid.com/forum/showthre...t=13006&page=2

You can download the '*JB Word components exporter.zip*' archive. It
contents the source code.

hf ;)


--
gg67
------------------------------------------------------------------------
gg67's Profile: http://www.excelforum.com/member.php...o&userid=33548
View this thread: http://www.excelforum.com/showthread...hreadid=336694

Peo Sjoblom wrote:
I am sure you will be very popular with people who make add-ins as part
of their living
I just hoped to be able to help somebody, in all modesty...


David, look at the bottom of the page. Or you can directly download the
attached file here.

hf ;)


+-------------------------------------------------------------------+
|Filename: JB Word components exporter.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4645 |
+-------------------------------------------------------------------+

--
gg67
------------------------------------------------------------------------
gg67's Profile: http://www.excelforum.com/member.php...o&userid=33548
View this thread: http://www.excelforum.com/showthread...hreadid=336694