Solved

VBA Excel - Code to unprotect VBA Project

Posted on 2010-09-01
7
1,177 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:NerishaB
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, I used the ideas to help me in m project.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now