Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Search All Databases in Folder for Words in Code Modules

From Wiki

Jump to: navigation, search

I would not trust this to run unattended, because you are likely to get all sorts of problems with passwords and such like.

This will search form and report code and modules, includimg class modules, but will not search comments. Once the code is found, you have all the information you need to change it.

  1. Sub SearchAllCode()
  2.     Dim ap As New Access.Application
  3.     Dim sfile As String, afind As Variant
  4.     Dim mdl As Object
  5.     Dim prcname As String
  6.     Dim lsline As Long, lscol As Long
  7.     Dim leline As Long, lecol As Long
  8.     Dim sline As String, r As Long
  9.     Dim i, j
  10.    
  11.     ap.Visible = True
  12.    
  13.     ''Words to find
  14.     afind = Split("msgbox,chair,ombo,Visible", ",")
  15.  
  16.     ''Directory to search
  17.     sfile = Dir("Z:\Docs\*.accdb")
  18.    
  19.     Do While sfile <> vbNullString
  20.         ''If you have a standard database password, you can pit it here. It will be ignored
  21.         ''for databases that do not require a password.
  22.  
  23.         ap.OpenCurrentDatabase "Z:\Docs\" & sfile, False, "pass"
  24.        
  25.         For i = 1 To ap.VBE.ActiveVBProject.VBComponents.Count
  26.    
  27.             Set mdl = ap.VBE.ActiveVBProject.VBComponents(i).CodeModule
  28.            
  29.             For j = 0 To UBound(afind)
  30.                 leline = mdl.CountOfLines
  31.                 ''object.Find(target, startline, startcol, endline, endcol
  32.                 ''[, wholeword] [, matchcase] [, patternsearch]) As Boolean
  33.                 ''The default is false for the three optional parameters.
  34.                 ''Finds first occurrence only
  35.                 If mdl.Find(afind(j), lsline, lscol, leline, lecol) Then
  36.      
  37.                     sline = mdl.Lines(lsline, Abs(leline - lsline) + 1)
  38.                     prcname = mdl.ProcOfLine(lsline, r)
  39.    
  40.                     Debug.Print mdl.Name
  41.                     Debug.Print prcname
  42.                     Debug.Print lsline
  43.                     Debug.Print sline
  44.                 End If
  45.             Next
  46.         Next
  47.         ap.CloseCurrentDatabase
  48.         sfile = Dir
  49.     Loop
  50.     ap.Quit
  51.     End Sub

Here is an alternative search, but it does not give you the means of manipulating the code once the line is found. However, it will return all occurrences and will search comments.

  1. Sub AlternativeSearch()
  2.     Dim ap As New Access.Application
  3.     Dim sfile As String, afind As Variant
  4.     Dim mdl As Object
  5.     Dim modtext As String, modarray As Variant
  6.     Dim leline As Long
  7.     Dim i, j, k
  8.    
  9.    
  10.     ap.Visible = True
  11.    
  12.     afind = Split("msgbox,chair,ombo,Visible", ",")
  13.     sfile = Dir("Z:\Docs\*.accdb")
  14.    
  15.     Do While sfile <> vbNullString
  16.         ap.OpenCurrentDatabase "Z:\Docs\" & sfile, False, "pass"
  17.        
  18.         For i = 1 To ap.VBE.ActiveVBProject.VBComponents.Count
  19.    
  20.             Set mdl = ap.VBE.ActiveVBProject.VBComponents(i).codemodule 'ap.Modules(mdlname)
  21.             leline = mdl.CountOfLines
  22.             modtext = mdl.Lines(1, leline)
  23.            
  24.             For j = 0 To UBound(afind)
  25.                 If InStr(modtext, afind(j)) > 0 Then
  26.                     Debug.Print "****" & afind(j) & " found in " & mdl.Name
  27.                     modarray = Split(modtext, vbCrLf)
  28.                     For k = 0 To UBound(modarray)
  29.                         If InStr(modarray(k), afind(j)) > 0 Then
  30.                             Debug.Print k
  31.                             Debug.Print modarray(k)
  32.                         End If
  33.                     Next
  34.                 End If
  35.             Next
  36.         Next
  37.         ap.CloseCurrentDatabase
  38.         sfile = Dir
  39.     Loop
  40.     ap.Quit
  41.     End Sub

835 Rating: 2.5/5 (31 votes cast)