VBIDE.VBProject / vbpProj.Protection = vbext_pp_locked

[Bilbo_Baggins_Esq]

HI ALL,

From an Excel Add-In (XLAM) a slightly modified version of this code will open an Excel workbook, and determine if the VBA Project is locked, capture the result as well as a few other attributes and put the results in a worksheet. Here I have pasted a trimmed down version minus the file open dialog box and writing of the results.

Sub CheckVBA()
Dim strDocPath As String
Dim docProj As Excel.Workbook
Dim vbpProj As VBIDE.VBProject
Dim Response As String

strDocPath = "C:\Workbook_Test.xls"

Set docProj = Workbooks.Open(strDocPath)
Set vbpProj = docProj.VBProject

If vbpProj.Protection = vbext_pp_locked Then
    Response = strDocPath & Chr(10) & Chr(10) & "The VBA Project is locked"
    Else
    Response = strDocPath & Chr(10) & Chr(10) & "The VBA Project is not locked"
End If

MsgBox Response

ActiveWorkbook.Close (False)

End Sub

With very slight modification, I can also use this code to check the VBA Project for Word and PPT files as well.

However, I am working now to try and modify the code to work for the VBA Project contained within an Access database, again running inside Excel.
(I know, I'd much rather be working in Access, but it not an option right now)

Is there anybody that could help me modify the above code so I can achieve the same result for an Access database VBA Project?

I have been trying various different object types and methods and I can't seem to get it right.
I keep getting "type mismatch" errors, "Object doesn't support method" errors, "Argument not optional" errors.
all at various different points depending on what I'm trying at that time.

It seems like I was closer to success earlier today, (it was making it farther in the code), but I've pecked and checked so much, to be honest, I have no idea what my earlier code looked like.

I've searched the Object Browser and yes, I can find the VBIDE section and the vbext_pp_locked value, but there are no samples to help me out.

I've searched these forums but it doesn't look like anybody has posted on this topic before.

I've searched the internet and it is EASY to find code for checking a workbook, but zilch for an Access database.

Please, can anybody help me out here?

 

[mdlueck]

However, I am working now to try and modify the code to work for the VBA Project contained within an Access database, again running inside Excel.

I have a head ache at thinking about what you are describing...

Do you actually mean running an Access VBA application "inside" an Excel spreadsheet?

I know of Allen Browne's CountLines which makes use of manipulating the VBA editor within Access. http://allenbrowne.com/vba-CountLines.html Perhaps that sample code would be helpful to you.

 

[Bilbo_Baggins_Esq]

oh my no, no no.

Not one of those people....
OH My, not trying to programatically modify code.

The code will be static and fire from a button on the ribbon inside Excel.

I provided a sample piece of code which achieves the same purpose for testing the VBIDE.Protection of Excel files.
I am trying to modify that code sample (above) so that is will run from Excel but instead of cehcking an Excel file, will check the VBIDE.Protection of an Access database.

 

[mdlueck]

I provided a sample piece of code which achieves the same purpose for testing the VBIDE.Protection of Excel files.
I am trying to modify that code sample (above) so that is will run from Excel but instead of cehcking an Excel file, will check the VBIDE.Protection of an Access database.

Excel files are acceptable to do such checks against from Excel VBA as "Excel is Excel".

This type of test you propose needs to activate Access VBA. So, must fire up Access to get Access to check on the protection of a file.

Excel is not Access. Just because Excel can import data from Access, that does not equate to being able to run Access VBA.

 

[Bilbo_Baggins_Esq]

Dear mdluek,

This type of test you propose needs to activate Access VBA.

This is why I have enabled the Access Object Model in the Excel Project's library references.
Thusly, creating an object within the Excel's VBA Project should allow me to run Access successfully from within Excel.
For this particular challenge, it appears the big question might me, just what type of object I need to create.
However, as is often the case, I could be completely wrong and it may actually be the methods I employ with the objects already created (or both).
I don’t know, that is why I’m seeking help from the world’s best set of Access forums.

So, must fire up Access to get Access to check on the protection of a file.

By working with the Access Object Model this is essentially what I am doing, even if it is from within Excel.

Excel is not Access. Just because Excel can import data from Access, that does not equate to being able to run Access VBA.

With greatest respect, I must say this is an extreme understatement of the capabilities of VBA.

In the past, in other projects, I have:
As well as operating each other from each other, from Excel and Access, I have integrated/operated Word, PowerPoint, and Outlook quite successfully.
I have also integrated Outlook into each of the other Office apps.

This is the first time I have tried to gather a property of the VBA Project so I appear to be stumbling, but I do believe it is possible given the correct object and method designation(s).

These forums, as well as others are rife with other examples where VB and VBA code have been used to activate and operate other applications, even hardware.

In another thread, which I did not have time to update today, I was asking for assistance in checking to see if an older formatted Access database is encoded (from within an Excel Project).
While I was not successful in detecting the exact property, with the help of a forum member, I was completely able to successfully create an Access Object, fire the Access File Open dialog, open a database, attempt to convert the database to a new format and watch the process for an error message indicating the file was encoded.
While it might not be the most graceful means to that end, it works and it is all run from within an Excel VBA Project.

I fully realize my skillset is not that of the masters of these forums.
However, I am not a newbie either.
When I come here, I always try to find 3 or 4 others who can be helped with what skills I do possess.
Even if all I manage to do is to ask some further qualifying questions.
I firmly believe in giving back to the communities.
I may not do it as gracefully as others might, but I usually manage to get the job done and hope that I manage to relieve at least a small sliver of the work load, as well as to assist others and add value to these terrific forums.

And so, if it is your gist that I need to have the Access Object model enabled within Excel in order to operate Access, please rest assured that I do.
However, if it is your gist that Access is Access and Excel is Excel, and thusly on that premise alone what I seek is not possible, it is with the greatest regard that I must respectfully disagree.

So, bearing in mind that I have SURELY enabled the Access VBA object model within Excel, does anybody have any clues or code or suggestions as to the type of objects I should use or the methods necessary to achieve this goal?

 

[mdlueck]

With greatest respect, I must say this is an extreme understatement of the capabilities of VBA.

Well, you seemed afraid to run the code I suggested to merely enumerate through the Access VBA modules in order to obtain the LOC count of the application...

oh my no, no no.

Not one of those people....
OH My, not trying to programatically modify code.

At least that code example was accessing the Access VBA IDE screens... I thought at least headed in the correct direction, however you seem to have thought not.

BTW: I drive both Excel spreadsheet and Word document creation both from Access driving. I have never used one Office product to snoop inside another application's data file. Example code of what I speak of:

Example Access VBA preparing a Word document using Range Bookmarks
http://www.access-programmers.co.uk/forums/showthread.php?t=236469

 

[Bilbo_Baggins_Esq]

Well, you seemed afraid to run the code I suggested to merely enumerate through the Access VBA modules in order to obtain the LOC count of the application...

At least that code example was accessing the Access VBA IDE screens... I thought at least headed in the correct direction, however you seem to have thought not.

Again with the greatest respect, I wouldn't suggest assuming facts not in evidence. I have indeed opened the page you provided and am reading through it to see if any of it might be useful for this goal.
Since I have the functional code to get the value, my hunch is, I'm looking for the object types created but so far, I've not found it. It almost looks as if not all of the code is there, but again, I'm still reading through it.

Since I neglected to mention it in my last post, please accept my apologies for not thanking you and mentioning I am reading through it. I hope it is heading in the right direction.

I have never used one Office product to snoop inside another application's data file.

Perhaps I am grossly underestimating or mis-representing my quest, but I did/do not really consider opening a file's VBA project to capture an attribute's value as "snooping... inside the data file".
It's not like I'm trying to parse through the binary or modify the actual code.

 

[mdlueck]

While a bit dated now, have you seen these URL's:

Locking Your Solution’s VBA Project
http://msdn.microsoft.com/en-us/library/office/aa165442(v=office.10).aspx

Securing Access Databases
http://msdn.microsoft.com/en-us/library/office/aa141451(v=office.10).aspx

 

[mdlueck]

Perhaps you will need to create an Access.CodeProject to get at that property of an Access DB file.

Other links which might be helpful:

VBA Extend
http://www.codeproject.com/Articles/229280/VBAExtend

CodeProject.Connection Property (Access)
http://msdn.microsoft.com/en-us/library/office/ff192857.aspx

 

[Bilbo_Baggins_Esq]

Yes, of couse the first link is where I originally located the code that got me started.
I was able to modify it to work for Excel quite nicely.

The second link I also have bookmarked as reference, but it doesn't really have anything I've found useful for this particular quest.

Here is a set of code I've been trying to work with:

Sub CheckDatabasseVBALocked_3()

Dim Response As String
Dim AccessPathIn As String
Dim AccessObj As Object
Dim AccessVBProj As VBIDE.VBProject

AccessPathIn = "C:\Database3.mdb"
Set AccessObj = CreateObject("Access.Application")
AccessObj.Application.OpenCurrentDatabase AccessPathIn, True

Set AccessVBProj = AccessObj.VBProject

If AccessVBProj.Protection = vbext_pp_locked Then
    Response = AccessPathIn & Chr(10) & Chr(10) & "The VBA Project is locked"
    Else
    Response = AccessPathIn & Chr(10) & Chr(10) & "The VBA Project is not locked"
End If

MsgBox Response

End Sub

However, when the code reaches "Set AccessVBProj = AccessObj.VBProject"this error fires:

Run-time error '438':
Object doesn't support this property or method

 

[mdlueck]

I found references to the Protection property deep in the VBA help which comes with Access 2007. It made it sound like to access it one must add:

Visual Basic for Applications Extensibility 5.3
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

With that reference added, now I get in teh Access VBA Immediate window:

? vbext_pp_locked
 1

Protection Property (VBA Add-In Object Model)
http://office.microsoft.com/en-us/access-help/HV080756670.aspx

http://msdn.microsoft.com/en-us/library/office/aa189282(v=office.10).aspx

The documentation for that add-in seems to be lacking.

Also, see page 24 of this PDF file for a code sample:
http://www.accessextended.com/downloads/docs/AccessAutomation.pdf

This looks to be the sought after prize possibly at last...

 

[Bilbo_Baggins_Esq]

mdlueck,
Very interesting stuff in the pdf.

Yes, I've seen and studies the extra two links.
yes, I do have the "Visual Basic for Applications Extensibility 5.3" added, and yes, from any applicaitons VBA Window you can enter vbext_pp_locked in the immediate window and get the applicable value.

Now this PDF is not something I have previously seen and on page 25 the writer appears to specifically be creating object(s) and then using the .properties to get the value.
they are object type(s) I've not yet tried so I'll see if I can plink with that for a bit.

If you're toying with it, and find the combination that will work running from Excel, wow that would be terrif!

 

[mdlueck]

This at least works from Access without any additional references required:

Option Compare Database
Option Explicit

Private cp As New Access.Application
Private Const mstrDbPath As String = "C:\Documents and Settings\c_mlueck\My Documents\Fandango\Schema Ideas2.accdb"
Private mintTotalLines As Integer
Private Const mstrCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;UserID=Admin;Data Source=C:\Documents and Settings\c_mlueck\My Documents\Fandango\Schema Ideas2.accdb;"

Public Sub Connect()
On Error GoTo Handler

  With cp
    .VBE.MainWindow.Visible = False
    .OpenCurrentDatabase mstrDbPath, True
    .Application.DoCmd.Minimize
  End With

ExitHere:
  Exit Sub

Handler:
  MsgBox Err.Number & VBA.vbCrLf & Err.Description
Resume ExitHere

End Sub

Private Sub CloseDb()
On Error Resume Next

  If Not (cp Is Nothing) Then
    cp.CloseCurrentDatabase
    cp.Application.Quit
    Set cp = Nothing
  End If

End Sub

Sub GetAllModules()
On Error GoTo Handler

  Dim CodeDoc As Object

  Call Connect

  For Each CodeDoc In cp.VBE.ActiveVBProject.VBComponents
    Debug.Print "Object: " & CodeDoc.Name & " Protection = " & CodeDoc.Collection.Parent.Protection
  Next CodeDoc

ExitHere:
  Call CloseDb
  Exit Sub

Handler:
  MsgBox Err.Number & VBA.vbCrLf & Err.Description
  Resume ExitHere

End Sub

Fire it via:

[B]GetAllModules[/B]
Object: modshared_uiutils, Protection = 0
Object: modshared_dbutils, Protection = 0
...

in the Immediate window.

 

[Bilbo_Baggins_Esq]

mdlueck,

THANKS! THANKS! THANKS!

Well, you’re right the code does run, and it does work, but only indirectly achieves the objective.
By that I mean it is trying to gather the protection property of a specific module, gets it if it can, or fires an error if it can’t (locked).
Essentially, the same thing could be achieved by trying to gather any property of any of the project’s objects, such as .name (or even really just try to capture the count of objects would also fail if the project is locked).
If the project is locked, it will fail and you could fire the same error box.
But all of these are indirect solutions which necessitate looking for and handling specific errors.


So, the PDF you found and the code you posted did get me on track to directly detecting the actual attribute without triggering and interpreting an error message.

By adding a reference to the “Microsoft Visual Basic for Applications Extensibility 5.3” this code actually does exactly what I’ve been seeking, albeit from Access…

Sub GetAllModules()
On Error GoTo Handler

  Call Connect

If cp.VBE.ActiveVBProject.Protection = vbext_pp_locked Then
    Debug.Print "Project is locked"
    Else
    Debug.Print "Project is NOT locked"
End If

ExitHere:
  Call CloseDb
  Exit Sub
Handler:
  Debug.Print Err.Number & VBA.vbCrLf & Err.Description
  Resume ExitHere

End Sub

I left the “Handler:” in but in truth, there is no error condition (as long as the references are made).
Now I have to figure out how to run it from Excel, but I feel confident about that.
I’m just out of time for today and will get back after this tomorrow.

THANKS FOR HANGING IN THERE WITH ME!

 

[Bilbo_Baggins_Esq]

Ok, so, the code I posted above, depends on the "connect" and "CloseDb" subs which are contained in mdlueck's posted at two places above^.
It runs in Access and does require “Microsoft Visual Basic for Applications Extensibility 5.3”.

However, as stated above, the clear goal was to get this achieved whilst running in Excel. Since I was out ot time yesterday, I put a pause and picked it up this morning.
Now I wish I had spent a few minutes last night do it, because really, that is all it took to finish this off in Excel.

So here, below, is the fully functional and self-contained (no reliance on external subs) code to run in Excel.
I would very much like to thank mdlueck for his assistance and hanging with me through this challenge!

Sub CheckDatabasseVBALocked()

'Create some variables
Dim AccessPathIn As String
Dim AccessObj As Access.Application

'Define the path
AccessPathIn = "I:\Documents\Access\Database3.accdb"

'Create the object in Excel
Set AccessObj = CreateObject("Access.Application")

'Open the file in the object
With AccessObj
    .VBE.MainWindow.Visible = False
    .OpenCurrentDatabase AccessPathIn, True
    .Application.DoCmd.Minimize
End With

'Do the test
If AccessObj.VBE.ActiveVBProject.Protection = vbext_pp_locked Then
    Debug.Print "Project IS locked"
    Else
    Debug.Print "Project IS NOT locked"
End If

'Close the file and clear the object
AccessObj.CloseCurrentDatabase
AccessObj.Application.Quit
Set AccessObj = Nothing


End Sub

 

[mdlueck]

Bravo Bilbo for managing to port the Access VBA to Excel VBA. Thank you for posting the working code example.

Thought: I believe if you substituted the value 1 for vbext_pp_locked and perhaps commented on the same LOC that vbext_pp_locked = 1, then you could get around the dependency on the "Microsoft Visual Basic for Applications Extensibility 5.3" reference.

Or you could define vbext_pp_locked in the same module, like I do for ADO constants:

ADO Constants for use with Late Binding ActiveX Data Objects 2.8 Library
http://www.access-programmers.co.uk/forums/showthread.php?t=243088