Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to Read Modules (A2K)

    I want to write a routine to read all VBA modules in a database, whether it is freestanding or attached to a form (or report, for that matter). What approach can I take? I know how to read the collections, the tabledefs and querydefs and so on, but the modules collection does not appear to carry the actual module contents, only some high-level information. What am I missing?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to Read Modules (A2K)

    Check out the Lines property of the Module object.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to Read Modules (A2K)

    Heck, give me a little more to go on than that! How do I make the declarations to access this property? I see modules as collections, not objects. So far I do not see a Lines property; if I had, I would not ask the question. How do I get to it?

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to Read Modules (A2K)

    OK, got it. Now, more fun: reading the module with DoCmd.OpenModule assumes we want to open a module in the current database. Is there a way to open/read a module in another database without leaving the one we are in? I don't need to change it necessarily (although that would be handy), but I do need to look at it from afar.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: VBA to Read Modules (A2K)

    Not sure what you need this for, but here is sample sub that will print the contents of any form module, report module, or standard or class module to the Debug window:

    Public Sub PrintModuleText(ByVal strObjName As String, _
    ByVal intObjType As AcObjectType)

    ' strObjName = name of object to open (form, report, module)
    ' intObjType = type of object - valid obj types:
    ' Const acForm = 2
    ' Const acReport = 3
    ' Const acModule = 5
    ' AcModuleType constants:
    ' Const acStandardModule = 0
    ' Const acClassModule = 1

    Dim mdl As Access.Module
    Dim n As Long
    Dim strObjType As String

    Select Case intObjType
    Case 2 ' Form
    DoCmd.OpenForm strObjName, acDesign
    Set mdl = Forms(strObjName).Module
    strObjType = "Form"

    Case 3 ' Report
    DoCmd.OpenReport strObjName, acViewDesign
    Set mdl = Reports(strObjName).Module
    strObjType = "Report"

    Case 5 ' Module
    DoCmd.OpenModule strObjName
    Set mdl = Modules(strObjName)
    strObjType = "Module"

    Case Else
    MsgBox "Invalid object type specified.", vbExclamation, "INVALID OBJECT"
    Set mdl = Nothing
    Exit Sub
    End Select

    n = mdl.CountOfLines
    Debug.Print "Object Name: " & strObjName & vbCrLf & _
    "Object Type: " & strObjType & vbCrLf & _
    "Module text:" & vbCrLf & _
    mdl.Lines(1, n)

    Select Case intObjType
    Case 2 ' Form
    DoCmd.Close acForm, strObjName
    Case 3 ' Report
    DoCmd.Close acReport, strObjName
    Case 5 ' Module
    DoCmd.Close acModule, strObjName
    End Select

    Set mdl = Nothing

    End Sub

    Note that the object must be open to be able to "read" the code module. You may be able to adapt this for your purposes, note that error-handling should be added. (If you open form or report w/o code module, it'll open form in design view and ask you if you want to save changes....).

    HTH

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA to Read Modules (A2K)

    Take a look at the OpenCurrentDatabase method. That will allow you to use automation to open another database and make it the "current" database, without closing the one you called it from.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: VBA to Read Modules (A2K)

    <P ID="edit" class=small>(Edited by MarkD on 19-Jul-03 09:24. Corrected bug in code example.)</P>As recommended, you can use OpenCurrentDatabase method to open another (invisible) instance of Access, then open objects in another database. Revised sample sub:

    Public Sub PrintModuleTextOther(ByVal strDbPath As String, _
    ByVal strObjName As String, _
    ByVal intObjType As AcObjectType)
    On Error GoTo Err_Handler

    ' strDbPath = full path to other db file to open - ex:
    '' C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb
    ' strObjName = name of object to open (form, report, or module)
    ' intObjType = type of object - AcObjectType enum:
    '' Const acForm = 2; Const acReport = 3; Const acModule = 5

    Dim app As Access.Application
    Dim mdl As Access.Module
    Dim n As Long
    Dim strObjType As String
    Dim strModType As String
    Dim strMsg As String
    Dim bFound As Boolean

    Set app = New Access.Application
    app.Visible = False
    app.OpenCurrentDatabase strDbPath, True

    Select Case intObjType
    Case 2 ' Form
    app.DoCmd.OpenForm strObjName, acDesign
    If app.Forms(strObjName).HasModule = True Then
    Set mdl = app.Forms(strObjName).Module
    strObjType = "Form"
    bFound = True
    Else
    strMsg = "The selected form (" & strObjName & ") does not have a code module."
    MsgBox strMsg, vbExclamation, "NO MODULE"
    bFound = False
    End If

    Case 3 ' Report
    app.DoCmd.OpenReport strObjName, acViewDesign
    If app.Reports(strObjName).HasModule = True Then
    Set mdl = app.Reports(strObjName).Module
    strObjType = "Report"
    bFound = True
    Else
    strMsg = "The selected report (" & strObjName & ") does not have a code module."
    MsgBox strMsg, vbExclamation, "NO MODULE"
    bFound = False
    End If

    Case 5 ' Module
    app.DoCmd.OpenModule strObjName
    Set mdl = app.Modules(strObjName)
    strObjType = "Module"
    bFound = True

    Case Else
    MsgBox "Invalid object type specified.", vbExclamation, "INVALID OBJECT"
    bFound = False
    End Select

    If bFound = True Then
    Select Case mdl.Type
    ' AcModuleType constants:
    Case 0
    strModType = "Standard Module" ' acStandardModule
    Case 1
    strModType = "Class Module" ' acClassModule
    End Select

    n = mdl.CountOfLines
    Debug.Print "Object Name: " & strObjName & vbCrLf & _
    "Object Type: " & strObjType & vbCrLf & _
    "Module Type: " & strModType & vbCrLf & _
    "Module text:" & vbCrLf & mdl.Lines(1, n)
    End If

    app.DoCmd.Close intObjType, strObjName
    app.CloseCurrentDatabase
    app.Quit

    Exit_Sub:
    Set app = Nothing
    Set mdl = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 7866 'Database not found or opened exclusively by other user
    strMsg = "The database specified does not exist, " & _
    "or is opened exclusively by another user."
    MsgBox strMsg, vbExclamation, "CANNOT OPEN DATABASE"
    Resume Exit_Sub
    Case 2102, 2103, 2516 'Form, Report, Module not found
    strMsg = "The object name and type specified does not exist."
    MsgBox strMsg, vbExclamation, "CANNOT OPEN OBJECT"
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "PRINT MODULE TEXT ERROR"
    Resume Exit_Sub
    End Select

    End Sub

    Note modified previous example to test if form or report has code module (HasModule property), and to indicate whether module is a standard or class module in case that is significant.. I tested this with Northwind.mdb. Example:

    PrintModuleTextOther "C:Program FilesMicrosoft OfficeOffice10SamplesNorthwind.mdb","Orders",acFor m

    The code module for Northwind "Orders" form was "printed" to debug window. Note if you open code module the VBE window will be briefly visible because the VB Editor opens in its own window. There are probably more direct ways to access a code module but these likely are not readily available to end-users.

    HTH

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: VBA to Read Modules (A2K)

    Looked at this one last time, here is simplified method that does not require opening object, thus eliminating annoying screen "flash" when a module is opened:

    Public Sub PrintModuleTextRev(ByVal strDbPath As String, _
    ByVal strObjName As String, _
    ByVal intObjType As AcObjectType)
    On Error GoTo Err_Handler

    Dim app As Access.Application
    Dim n As Long
    Dim strObjType As String
    Dim strModType As String
    Dim strMsg As String

    Set app = New Access.Application
    app.Visible = False
    app.OpenCurrentDatabase strDbPath, True

    Select Case intObjType
    Case 2 ' Form
    strObjType = "Form"
    strObjName = "Form_" & strObjName

    Case 3 ' Report
    strObjType = "Report"
    strObjName = "Report_" & strObjName

    Case 5 ' Module
    strObjType = "Module"
    End Select

    Select Case app.VBE.ActiveVBProject.VBComponents.Item(strObjNa me).Type
    Case 1 ' vbext_ct_StdModule
    strModType = "Standard Module"
    Case 2 ' vbext_ct_ClassModule
    strModType = "Class Module"
    Case Else
    ' Form and Report = VBComponent Type 100 - not listed
    strModType = "MS Access Class Object"
    End Select

    n = app.VBE.ActiveVBProject.VBComponents.Item(strObjNa me).CodeModule.CountOfLines
    Debug.Print "Object Name: " & strObjName & vbCrLf & _
    "Object Type: " & strObjType & vbCrLf & _
    "Module Type: " & strModType & vbCrLf & _
    "Module text:" & vbCrLf & app.VBE.ActiveVBProject.VBComponents.Item(strObjNa me).CodeModule.Lines(1, n)

    app.CloseCurrentDatabase
    app.Quit

    Exit_Sub:
    Set app = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 9 'Subscript Out of Range (ie, VBComponent not found for specified object name)
    strMsg = "Code module for object name specified was not found in database specified."
    MsgBox strMsg, vbExclamation, "OBJECT NOT FOUND"
    Case 7866 'Database not found or opened exclusively by other user
    strMsg = "The database specified does not exist, " & _
    "or is opened exclusively by another user."
    MsgBox strMsg, vbExclamation, "CANNOT OPEN DATABASE"
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "PRINT MODULE TEXT ERROR"
    End Select
    Resume Exit_Sub
    End Sub

    For further info, set a reference to VB Extensibility library (VBIDE) (VB6EXT.OLB) and examine the objects, properties & methods available.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •