如何搜索 VBA 代码文件 [英] How to search through VBA code files

问题描述

我刚开始在一家新公司工作，以前的开发人员在该公司创建了许多自动化任务.当然，几乎没有任何文档，我也没有机会与之前的开发人员合作，所以现在我正在尝试筛选所有这些流程，以寻找可以修改某些特定文件的流程.

I just started a job with a new company where previous developers had created many automated tasks. Of course, there is virtually no documentation and I didn't have a chance to work with the previous developer so now I'm trying to sift through all these processes looking for one that modifies some specific files.

我在 SQL 中编写了所有存储过程的脚本并使用了搜索工具，但没有找到我要找的内容，所以现在我想知道我需要的进程是否位于使用的许多 Access 数据库之一中.使用 SQL Server，很容易编写一个 C# 应用程序来编写 procs 脚本，以便我可以搜索它们，但是使用 Access 看起来我只能单独打开每个数据库来搜索代码文件.

I've scripted all the stored procs in SQL and used a search tool and didn't find what I was looking for, so now I am wondering if the process I need is located in one of many Access databases that are used. With SQL Server, it was easy to write a C# app to script the procs so I could search through them, but with Access it looks like I'm confined to opening each db individually to search through the code files.

有没有办法以编程方式搜索 VBA 代码文件?

Is there any way to programatically search through VBA code files?

推荐答案

如果您对在 Access 数据库文件中搜索代码模块感兴趣，您可以使用 VBE 对象模型.本示例在当前数据库的ActiveVBProject 的所有模块中搜索一个词.如果数据库包含多个 VBProject，您可以枚举 VBProjects 集合并按名称一次搜索一个项目:

If your interest is searching code modules in an Access database file, you can use the VBE object model. This sample searches for a word in all the modules of the ActiveVBProject of the current database. If the database includes more than one VBProject, you can enumerate the VBProjects collection and search the projects one at a time by name:

For Each objComponent In Application.VBE.VBProjects(ProjName).VBComponents

或者，如果您更喜欢通过编号而不是名称来引用项目，请注意编号以 1 而不是 0 开头.

Or if you prefer to reference the project by number rather than name, just be aware the numbering starts with 1 rather than 0.

Public Sub findWordInModules(ByVal pSearchWord As String)
    'Dim objComponent As VBComponent
    ' VBComponent requires reference to Microsoft Visual Basic
    ' for Applications Extensibility; use late binding instead:
    Dim objComponent As Object
    Dim strMessage As String
    Dim strModuleList As String

    strModuleList = vbNullString
    For Each objComponent In Application.VBE.ActiveVBProject.VBComponents
        If objComponent.CodeModule.Find(pSearchWord, 1, 1, -1, -1) = True Then
            strModuleList = strModuleList & "; " & objComponent.Name
        End If
    Next objComponent
    strMessage = "Text '" & pSearchWord & "' found in "
    If Len(strModuleList) > 0 Then
        strMessage = strMessage & "modules: " & Mid(strModuleList, 3)
    Else
        strMessage = strMessage & "no modules"
    End If
    Debug.Print strMessage
End Sub

查看该 Find 方法的 Access 帮助主题；您可能更喜欢与我使用的不同的选项.

Review the Access help topic for that Find method; you may prefer different options than I used.

如果您想定位多个 db 文件并搜索每个文件中的模块，您可以使用 OpenDatabase 方法自动执行此操作.我会把那部分的细节留给你.

If you want to target multiple db files and search the modules in each, you could automate this using the OpenDatabase method. I'll leave the details of that part up to you.

这篇关于如何搜索 VBA 代码文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！