我将调查许多Acccess应用程序,如果可能的话,它将使我的生活更容易制作脚本.
我的主要目标是在MS Access表单代码/模块中搜索某些单词(例如dog,cat,cow等)并返回包含该单词的模块名称. (或者至少如果存在或不存在则返回)
据我了解,内置的Find函数不会查找多个单词,只需单个单词.
我不希望把每个字放在那里,然后做一个全部查找,因为这将耗费大量时间.
你会如何解决这个问题？
解决方法
你可以使用这样的东西,但它不会无人看管,因为你可能会遇到各种各样的密码等问题.
改写
Sub SearchAllCode()
Dim ap As New Access.Application
Dim sfile As String,afind As Variant
Dim mdlname As String,mdl As Object
Dim prcname As String
Dim lsline As Long,lscol As Long
Dim leline As Long,lecol As Long
Dim sline As String,r As Long
Dim i,j
ap.Visible = True
afind = Split("msgBox,chair,ombo,Visible",",")
sfile = Dir("Z:\Docs\*.accdb")
Do While sfile <> vbNullString
ap.OpenCurrentDatabase "Z:\Docs\" & sfile,False,"pass"
For i = 1 To ap.VBE.ActiveVBProject.VBComponents.Count
Set mdl = ap.VBE.ActiveVBProject.VBComponents(i).CodeModule
For j = 0 To UBound(afind)
leline = mdl.CountOfLines
''object.Find(target,startline,startcol,endline,endcol
''[,wholeword] [,matchcase] [,patternsearch]) As Boolean
''The default is false for the three optional parameters.
''Finds first occurrence only
If mdl.Find(afind(j),lsline,lscol,leline,lecol) Then
sline = mdl.Lines(lsline,Abs(leline - lsline) + 1)
prcname = mdl.ProcOfLine(lsline,r)
Debug.Print mdl.Name
Debug.Print prcname
Debug.Print lsline
Debug.Print sline
End If
Next
Next
ap.CloseCurrentDatabase
sfile = Dir
Loop
ap.Quit
End Sub
这是一个替代搜索,但一旦找到该行,它就不会为您提供操作代码的方法.
Sub AlternativeSearch()
Dim ap As New Access.Application
Dim sfile As String,afind As Variant
Dim mdl As Object
Dim modtext As String,modarray As Variant
Dim leline As Long
Dim i,j,k
ap.Visible = True
afind = Split("msgBox,"pass"
For i = 1 To ap.VBE.ActiveVBProject.VBComponents.Count
Set mdl = ap.VBE.ActiveVBProject.VBComponents(i).codemodule 'ap.Modules(mdlname)
leline = mdl.CountOfLines
modtext = mdl.Lines(1,leline)
For j = 0 To UBound(afind)
If InStr(modtext,afind(j)) > 0 Then
Debug.Print "****" & afind(j) & " found in " & mdl.Name
modarray = Split(modtext,vbCrLf)
For k = 0 To UBound(modarray)
If InStr(modarray(k),afind(j)) > 0 Then
Debug.Print k
Debug.Print modarray(k)
End If
Next
End If
Next
Next
ap.CloseCurrentDatabase
sfile = Dir
Loop
ap.Quit
End Sub
