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

NerishaBAsked:
Who is Participating?
 
sijpieConnect With a Mentor Commented:
Sorry NerishaB, I have been spouting rubbish. Upon reading the code a bit better, the GetModuleWorkbook function first checks to see if the workbook is already open. (wkb will point to something). wkb gets checked, and if nothing then it will open it from the given path.
Then it will return the workbook handle.

So the code was correct: if wbk is nothing then

Your problem is then with the second function UnprotectVBProject.

Two things:
1) does your code (at the very top of the module) start with 'Option Explicit' ? If not, add that in. It will force that each variable and function gets declared, so you debug a lot quicker.
2) Sendkeys will not wait unless specified so the code may need to be modified, as I don't know it the VBA Module is an external application. Experiment with the True or False (default)
  SendKeys "~" & strPassword & "~", True

Open in new window

0
 
TracyVBA DeveloperCommented:
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

0
 
NerishaBAuthor Commented:
Thanks.  The syntax is now correct, but it does not unprotect the VBA Project. :-(  DO you have any ideas on this?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
sijpieCommented:
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

0
 
NerishaBAuthor Commented:
What I dont get, is why is wbk Nothing?? The location that I specified is correct, I double checked...
0
 
sijpieCommented:
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
0
 
NerishaBAuthor Commented:
Thanks, I used the ideas to help me in m project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.