asked on

VBA Excel - Code to unprotect VBA Project

Hi,

I am trying to open a workboko that has it's VBA code protected. I have code to unprotect this, but I am having trouble calling this procedure properly. I am new to VBA, so am not very familiar with the syntax. Please see my code below, and tell me where I am going wrong.

I am trying to call the UnprotectVBProject in the GetModuleWorkbook() procedure.

Function GetModuleWorkbook() As Workbook
    Const cstrWORKBOOK_PATH As String = "C:\Netserver\oztech_dev_oz\oz_excel\"
    Const cstrWORKBOOK_NAME As String = "oz_main.xls"
    
    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks(cstrWORKBOOK_NAME)
    If wbk Is Nothing Then
         Dim secAutomation As MsoAutomationSecurity
            secAutomation = Application.AutomationSecurity
            Application.AutomationSecurity = msoAutomationSecurityForceDisable
            If UnprotectVBProject(wbk, "fYb42248Z9") Then
            Set wbk = Workbooks.Open(cstrWORKBOOK_PATH & cstrWORKBOOK_NAME, , ReadOnly:=True, Password:="fYb42248Z9", IgnoreReadOnlyRecommended:=True)
            End If
    End If
    Set GetModuleWorkbook = wbk
End Function
Sub UnprotectVBProject(ByRef objWorkbook As Workbook, ByVal strPassword As String)

  Set Application.VBE.ActiveVBProject = objWorkbook.VBProject
  SendKeys "~" & strPassword & "~"

End Sub

Open in new window

Tracy

You can't do this because is not a boolean:

If UnprotectVBProject(wbk, "fYb42248Z9") Then

You would have to change this:
Sub UnprotectVBProject(ByRef objWorkbook As Workbook, ByVal strPassword As String)

to this:
Function UnprotectVBProject(ByRef objWorkbook As Workbook, ByVal strPassword As String) As Boolean

and then add this at the end:
UnprotectVBProject = True

See below.

Function GetModuleWorkbook() As Workbook
    Const cstrWORKBOOK_PATH As String = "C:\Netserver\oztech_dev_oz\oz_excel\"
    Const cstrWORKBOOK_NAME As String = "oz_main.xls"
    
    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks(cstrWORKBOOK_NAME)
    If wbk Is Nothing Then
         Dim secAutomation As MsoAutomationSecurity
            secAutomation = Application.AutomationSecurity
            Application.AutomationSecurity = msoAutomationSecurityForceDisable
            If UnprotectVBProject(wbk, "fYb42248Z9") Then
            Set wbk = Workbooks.Open(cstrWORKBOOK_PATH & cstrWORKBOOK_NAME, , ReadOnly:=True, Password:="fYb42248Z9", IgnoreReadOnlyRecommended:=True)
            End If
    End If
    Set GetModuleWorkbook = wbk
End Function
Function UnprotectVBProject(ByRef objWorkbook As Workbook, ByVal strPassword As String) As Boolean

  Set Application.VBE.ActiveVBProject = objWorkbook.VBProject
  SendKeys "~" & strPassword & "~"
  UnprotectVBProject = True
End Sub

Open in new window

NerishaB

ASKER

Thanks. The syntax is now correct, but it does not unprotect the VBA Project. :-( DO you have any ideas on this?

sijpie

Why do you want to unprotect your code? Seems counterproductive to me.
Anyway you are trying to run the unprotect code when wbk is Nothing, ie the wbk wasn't found

 ...
   Set wbk = Workbooks(cstrWORKBOOK_NAME)
    If Not wbk Is Nothing Then
...

Open in new window

NerishaB

ASKER

What I dont get, is why is wbk Nothing?? The location that I specified is correct, I double checked...

sijpie

NO, you are correct: wbk is not nothing. And that is why the code in the if statement is not runniing, because it only runs whe it is nothing in your code.

So that is why you have to use
if NOT wbk is nothing then

ASKER CERTIFIED SOLUTION

sijpie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NerishaB

ASKER

Thanks, I used the ideas to help me in m project.