Solved List module name

[Superpat]

Hello,
I have two options to list module name :
The first list only the current module of the database, the second can list module outside of the database open.
Is there a solution to list the current module or an exterior module with the same code.

Public Sub listSubFuncVBA10(PathBase As String)
'listSubFuncVBA10("D:\Dropbox\Access\Travail\OK\Nav_Courant_10-20.accdb")
'Ne peut lister que le module courant
    Dim i As Long, j As Long, m As Long
    Dim lngCount As Long                                                    ' Nombre de ligne du module
    Dim sModules As String
    For i = 1 To Application.VBE.VBProjects(1).VBComponents.Count
        ' Nom du Formulaire, du Report, du Module
        sModules = Application.VBE.VBProjects(1).VBComponents.Item(i).Name
        Debug.Print vbCrLf & i & " " & Application.VBE.VBProjects(1).VBComponents.Item(i).Name
        ' Nombre de ligne du module.
        lngCount = Application.VBE.VBProjects(1).VBComponents.Item(i).CodeModule.CountOfLines
        Debug.Print "   Nombre de lignes dans le module " & sModules & " : " & lngCount
        ' Nombre de ligne dans la section déclaration du module.
        ' On boucle pour donner le nom des modules
    Next
End Sub

Function Liste_Procedures_Fonctions_VBA10(PathBase As String) As Boolean
'?Liste_Procedures_Fonctions_VBA10("D:\Dropbox\Access\Travail\OK\Nav_Courant_10-20.accdb")
'?Liste_Procedures_Fonctions_VBA10("D:\Dropbox\Access\Travail\OK\Variables\variables - 2.accdb")
'On ne peut lister qu'un module exterieur
    'Dim strSQL As String
    Dim Accmodule As Module
    Dim i As Integer
    Dim oAccess As New Access.Application
    On Error GoTo Fin
    With oAccess
        .Visible = False
        .OpenCurrentDatabase (PathBase)
    End With
    For i = 1 To oAccess.VBE.VBProjects(1).VBComponents.Count
        With oAccess.VBE.VBProjects(1).VBComponents.Item(i).CodeModule
                Debug.Print oAccess.VBE.VBProjects(1).VBComponents.Item(i).CodeModule
                'Debug.Print oAccess.VBE.VBProjects(1).VBComponents.Count
                Debug.Print oAccess.VBE.VBProjects(1).VBComponents.Item(i).CodeModule.CountOfLines
        End With
    Next i
    MsgBox "Fin : " & i
    oAccess.DoCmd.Close , , acSaveYes
    Set oAccess = Nothing
    Exit Function
Fin:
    Resume Next
End Function

 

[arnelgp]

you already have the code or just create a New Sub/Function and depending on the parameters passed,
call the pertinent function/sub:

Public Sub ListModules(Optional Byval dbPath As String = "")
    If Len(dbPath) = 0 Or dbPath = Currentdb.Name Then
        'currentdb
        Call listSubFuncVBA10("")
    Else
        'external db
        Call Liste_Procedures_Fonctions_VBA10(dbPath)
    End If
End Sub

 

[cheekybuddha]

You can make the PathBase argument optional and test its presence:

Function Liste_Procedures_Fonctions_VBA10(Optional PathBase As String = vbNullString) As Boolean
'?Liste_Procedures_Fonctions_VBA10("D:\Dropbox\Access\Travail\OK\Nav_Courant_10-20.accdb")
'?Liste_Procedures_Fonctions_VBA10("D:\Dropbox\Access\Travail\OK\Variables\variables - 2.accdb")
'On ne peut lister qu'un module exterieur
    'Dim strSQL As String
    Dim Accmodule As Module
    Dim i As Integer
    Dim oAccess As Access.Application
    On Error GoTo Fin

    If Len(PathBase) > 0 Then
        Set oAccess = New Access.Application
        With oAccess
            .Visible = False
            .OpenCurrentDatabase (PathBase)
        End With
    Else
      Set oAccess = Application ' Current Application instance
    End If
    
    For i = 1 To oAccess.VBE.VBProjects(1).VBComponents.Count
        With oAccess.VBE.VBProjects(1).VBComponents.Item(i).CodeModule
                Debug.Print oAccess.VBE.VBProjects(1).VBComponents.Item(i).CodeModule
                'Debug.Print oAccess.VBE.VBProjects(1).VBComponents.Count
                Debug.Print oAccess.VBE.VBProjects(1).VBComponents.Item(i).CodeModule.CountOfLines
        End With
    Next i
    MsgBox "Fin : " & i
    oAccess.DoCmd.Close , , acSaveYes
    Set oAccess = Nothing
    Exit Function
Fin:
    Resume Next
End Function

Then, to run on the current database, use:
Call Liste_Procedures_Fonctions_VBA10

Or, for a different database, use:
Call Liste_Procedures_Fonctions_VBA10("C:\Path\To\Your.accdb")

(Nb. Untested!)

 

[Superpat]

Thanks very much @arnelgp and @cheekybuddha It's what I want.

 

[Josef P.]

Note:

oAccess.VBE.VBProjects(1)

I would additionally check whether the appropriate VBProject is used.
=> compare VBProjectReference.FileName with CurrentDb.Name (UNC path).

 

[Pat Hartman]

This is part of a procedure I use that needs to reference both the currentdb and a different db at the same time:

    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    db.Containers.Refresh

    Set td = ThisDB.TableDefs!tbltables
    Set rs = td.OpenRecordset
    Set tdFields = ThisDB.TableDefs!tbltableFields
    Set rsFields = tdFields.OpenRecordset
    Set tdIndexes = ThisDB.TableDefs!tbltableIndexes
    Set rsIndexes = tdIndexes.OpenRecordset

So - it shows that you can set the db to either the current db or the remote db. That means you can use the same code by simply passing in the path to a db or leaving it empty.

 

[cheekybuddha]

That means you can use the same code by simply passing in the path to a db or leaving it empty.

Thanks for confirming what I posted above

 

[Superpat]

Thanks for confirming what I posted above

I take the option of @cheekybuddha, It works perfectly. Sorry for my last reponse, I think it was the reponse of @Pat Hartman

 

[Pat Hartman]

Thanks for confirming what I posted above

You're welcome But, my solution used a simpler method. I wasn't confirming yours per se. I'm sure yours works fine. VBA is way too flexible in how you can do things if you ask me and I'm not going to examine the two to see if there are differences in performance or capabilities. My code comes from an app I built to document other databases. It captures information about the schema in the other database and stores it in tables in the local database so reports can be created and used as documentation or to help figure out how complex a change might be. The code in my app only deals with tables, queries, indexes, and relationships.

 

[cheekybuddha]

my solution used a simpler method

How so?

 

[ebs17]

An analysis of a database schema is completely different than an analysis of a VBA project and a completely different topic, so unrelated here.

 

[cheekybuddha]

An analysis of a database schema is completely different than an analysis of a VBA project and a completely different topic, so unrelated here.

@Pat Hartman shows how to use an object (Database object) from either the current database or a different database, so whilst @Superpat is looking to use the Application object, it's not totally unrelated.

However, I don't see how @Pat Hartman does anything different from what was suggested in Post #3, so wondering what I missed?

(She does seem to have confused the object variables in the code snippet though ThisDB/db)

 

[ebs17]

deals with tables, queries, indexes, and relationships

These are elements of SQL, i.e. the database engine. While Jet/ACE is tightly integrated with Access, it is still something other than an Access application. The same commands via DAO or ADODB can also be issued from Excel, VBScript and others.

VBA projects and database engines are fundamentally different things. To analyze VBA, you need an Access application object as a starting point, never a DAO reference or an ADODB connection.

Application objects, DAO references or ADODB connections are all objects, but they are not interchangeable.

 

[cheekybuddha]

I know. I explained that.

The issue is not about the actual objects here though.

It's about how to use a choice of two different instances of an object in a single piece of code.

 

[cheekybuddha]

@Superpat - this recent blog post by Daniel Pineault may be of interest to you.