Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VBA Excel - Code to unprotect VBA Project

Posted on 2010-09-01
7
1,222 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:NerishaB
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 33578489
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
 

Author Comment

by:NerishaB
ID: 33584088
Thanks.  The syntax is now correct, but it does not unprotect the VBA Project. :-(  DO you have any ideas on this?
0
 
LVL 6

Expert Comment

by:sijpie
ID: 33585092
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:NerishaB
ID: 33585236
What I dont get, is why is wbk Nothing?? The location that I specified is correct, I double checked...
0
 
LVL 6

Expert Comment

by:sijpie
ID: 33585430
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
 
LVL 6

Accepted Solution

by:
sijpie earned 500 total points
ID: 33586284
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
 

Author Closing Comment

by:NerishaB
ID: 33700664
Thanks, I used the ideas to help me in m project.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question