VBA to Read Modules (A2K)

Topic

New Reply

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?

Reply | Quote

Replies

Check out the Lines property of the Module object.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

(Edited by MarkD on 19-Jul-03 09:24. Corrected bug in code example.)

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”,acForm

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

Reply | Quote

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(strObjName).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(strObjName).CodeModule.CountOfLines
Debug.Print “Object Name: ” & strObjName & vbCrLf & _
“Object Type: ” & strObjType & vbCrLf & _
“Module Type: ” & strModType & vbCrLf & _
“Module text:” & vbCrLf & app.VBE.ActiveVBProject.VBComponents.Item(strObjName).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.

Reply | Quote

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

Reply | Quote