Displaying the names of all of the modules in the database 2

[ddiamond]

I have the following code:

Public Sub test()
  Dim Mods As Modules
  Dim Mod1 As Module
  
  Set Mods = Application.Modules
  
  For Each Mod1 In Mods
    Debug.Print Mod1.Name
  Next
  
End Sub

Can anyone tell me why this only lists one module instead of all of them?

 

[Remou]

Application.Modules contains all open standard modules and class modules in a Microsoft Access database.

Use CurrentProject.AllModules to get a list.

For Each mdl In CurrentProject.AllModules 
  Debug.Print mdl.Name
Next

 

[ddiamond]

Thanks Remou. Access 97 does not have the CurrentProject object. Does anyone know how to do this in Access 97?

 

[Remou]

Joshua J. Kugler's post in:

http://groups.google.ie/group/micro..._frm/thread/a1119b85f474543a/cff1fff379f7ccd5

has a list method that may suit.

 

[ddiamond]

Thanks Remou. Their code snipet worked.

'List modules
Sub ListMods()
  Dim db As Database
  Dim ModuleN As Document
  
  Set db = CurrentDb
  For Each ModuleN In db.Containers!Modules.Documents
    Debug.Print ModuleN.Name
  Next ModuleN
End Sub

 

[ddiamond]

What I really want to do is display the code behind each module. I believe the module object contains this information, but the document object does not. Now if all of the modules were open, then the application.modules collection would work. Is there anyway with VBA to open all of the closed modules?

 

[Remou]

The way I would do this (Access 2000) is:
DoCmd.OpenModule ModuleN.Name
and according to:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/web/003.asp

Access 97 seems to have the same command.

 

[ddiamond]

Here's my final code.

Sub ListMods()
  Dim db As Database
  Dim ModDoc As Document
  Dim ModN As Module
  
  Set db = CurrentDb
  For Each ModDoc In db.Containers!Modules.Documents
    DoCmd.OpenModule ModDoc.Name
  Next
  
  For Each ModN In Modules
    Debug.Print ""
    Debug.Print "Module " & ModN.Name
    Debug.Print ModN.Lines(1, ModN.CountOfLines)
  Next
  
End Sub

 

[ddiamond]

It looks like a similar approach can be taken with forms and reports. Does anyone know if something like this is possible with macros?

 

[Remou]

Did you miss this bit in Joshua J. Kugler's post:

'List macros 
Sub ListMac() 
Set db = CurrentDb 
For Each MacroN In db.Containers!Scripts.Documents 
Debug.Print MacroN.Name 
Next MacroN 
End Sub

Or had you something fancier in mind?

 

[ddiamond]

That lists the macro names. My real goal is to write a search and replace. I was foolish enough to hard code the path in many-many transfertext macros. Now I need to change the path, and I am hoping I won't have to do it by hand.

 

[JerryKlmns]

Just to mention this query

SELECT [Name]
FROM MSysObjects
WHERE Type=-32761

and the undocumented Application.SaveAsText method along with
Application.LoadFromText with the most recent thread705-1175611

 

[ddiamond]

Thanks Jerry. I'll give it a try. In theory I could save it to text files, then do my search and replace, then reload them using the LoadAsText method. Sounds a bit risky, but that is what testing is for.

 

[JerryKlmns]

ddiamond

Well, right now I 'm doing exactly what you described to be "theory". Aint we having fun, or what?

 

[ddiamond]

Jerry,

Can you post the code you used to load the macros back in?

- Dan

 

[JerryKlmns]

LoadFromText acMacro,"YourMacroName", "c:\path\SavedMacroName.xcs"

LoadFromText acModule, "YourModuleName", "c:\path\SavedModuleName.xcm"