将 MS Access 表单和类/模块递归导出到文本文件? [英] Exporting MS Access Forms and Class / Modules Recursively to text files?

问题描述

我在一个古老的留言板上发现了一些代码，可以很好地从类、模块和表单中导出所有 VBA 代码(见下文):

I found some code on an ancient message board that nicely exports all of the VBA code from classes, modules and forms (see below):

Option Explicit
Option Compare Database
Function SaveToFile()                  'Save the code for all modules to files in currentDatabaseDirCode

Dim Name As String
Dim WasOpen As Boolean
Dim Last As Integer
Dim I As Integer
Dim TopDir As String, Path As String, FileName As String
Dim F As Long                          'File for saving code
Dim LineCount As Long                  'Line count of current module

I = InStrRev(CurrentDb.Name, "")
TopDir = VBA.Left(CurrentDb.Name, I - 1)
Path = TopDir & "" & "Code"           'Path where the files will be written

If (Dir(Path, vbDirectory) = "") Then
  MkDir Path                           'Ensure this exists
End If

'--- SAVE THE STANDARD MODULES CODE ---

Last = Application.CurrentProject.AllModules.Count - 1

For I = 0 To Last
  Name = CurrentProject.AllModules(I).Name
  WasOpen = True                       'Assume already open

  If Not CurrentProject.AllModules(I).IsLoaded Then
    WasOpen = False                    'Not currently open
    DoCmd.OpenModule Name              'So open it
  End If

  LineCount = Access.Modules(Name).CountOfLines
  FileName = Path & "" & Name & ".vba"

  If (Dir(FileName) <> "") Then
    Kill FileName                      'Delete previous version
  End If

  'Save current version
  F = FreeFile
  Open FileName For Output Access Write As #F
  Print #F, Access.Modules(Name).Lines(1, LineCount)
  Close #F

  If Not WasOpen Then
    DoCmd.Close acModule, Name         'It wasn't open, so close it again
  End If
Next

'--- SAVE FORMS MODULES CODE ---

Last = Application.CurrentProject.AllForms.Count - 1

For I = 0 To Last
  Name = CurrentProject.AllForms(I).Name
  WasOpen = True

  If Not CurrentProject.AllForms(I).IsLoaded Then
    WasOpen = False
    DoCmd.OpenForm Name, acDesign
  End If

  LineCount = Access.Forms(Name).Module.CountOfLines
  FileName = Path & "" & Name & ".vba"

  If (Dir(FileName) <> "") Then
    Kill FileName
  End If

  F = FreeFile
  Open FileName For Output Access Write As #F
  Print #F, Access.Forms(Name).Module.Lines(1, LineCount)
  Close #F

  If Not WasOpen Then
    DoCmd.Close acForm, Name
  End If
Next
MsgBox "Created source files in " & Path
End Function

但是，此代码并没有解决我的问题，因为我有 110 ms-access *.mdb 需要将 vba 从中导出到适合 grepping 的文本文件中.

However, this code does not solve my problem since I have 110 ms-access *.mdb's that I need to export the vba from into text files suitable for grepping.

我感兴趣的 110 个文件的路径已经存储在一个表中，我的代码已经递归地获取了这些信息(以及其他一些过滤)......所以递归部分完成了.

The paths to the 110 files I'm interested in are already stored in a table, and my code already gained this information recursively (along with some other filtering)...so the recursive part is done.

这些文件中的大多数是由单一访问用户安全文件(.mdw)打开的，我尝试了几种打开它们的方法.当我在这些目录中搜索链接表时，ADO 和 ADOX 工作得很好……但是上面的代码涉及 在您要从中导出数据的数据库中，我希望能够从一个单独的数据库中执行此操作打开所有 mdb 并在每个上面执行导出.

Most of these files are opened by a single access user security file, an .mdw and I have tried several methods of opening them. ADO and ADOX worked great when I was searching for linked tables in these directories...but the code above involves being inside the database you are exporting the data from, and I want to be able to do this from a separate database that opens all of the mdbs and performs the export on each of them.

我在这方面的尝试之一涉及使用 PrivDBEngine 类从外部连接到数据库，但它不允许我访问应用程序对象，而这正是上述导出代码所需要的.

One of my attempts at this involved using the PrivDBEngine class to connect to the databases externally, but it doesn't allow me to access the Application object which is what the export code above requires.

Private Sub exportToFile(db_path As String, db_id As String, loginInfo As AuthInfoz, errFile As Variant)

    Dim pdbeNew As PrivDBEngine
    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Dim rst As DAO.Recordset

    Dim cn As ADODB.Connection ' ADODB.Connection
    Dim rs As ADODB.Recordset ' ADODB.Recordset
    Dim strConnect As String
    Dim blnReturn As Boolean

    Dim Doc              As Document
    Dim mdl              As Module
    Dim lngCount         As Long
    Dim strForm          As String
    Dim strOneLine       As String
    Dim sPtr             As Integer

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set exportFile = fso.CreateTextFile("E:TicketsCSN1006218vbacode" & db_id & ".txt", ForAppending)

    ' Export stuff...

    On Error GoTo errorOut

    Set pdbeNew = New PrivDBEngine
    With pdbeNew
        .SystemDB = loginInfo.workgroup
        .DefaultUser = loginInfo.username
        .DefaultPassword = loginInfo.password
    End With


    Set ws = pdbeNew.Workspaces(0)


    Set db = ws.OpenDatabase(db_path)

    For Each Doc In db.Containers("Modules").Documents
        DoCmd.OpenModule Doc.Name
        Set mdl = Modules(Doc.Name)

        exportFile.WriteLine ("---------------------")
        exportFile.WriteLine ("Module Name: " & Doc.Name)
        exportFile.WriteLine ("Module Type: " & mdl.Type)
        exportFile.WriteLine ("---------------------")

        lngCount = lngCount + mdl.CountOfLines

        'For i = 1 To lngCount
        '    strOneLine = mdl.Lines(i, 1)
        '    exportFile.WriteLine (strOneLine)
        'Next i

        Set mdl = Nothing
        DoCmd.Close acModule, Doc.Name
    Next Doc

Close_n_exit:

    If Not (db Is Nothing) Then
        Call wk.Close
        Set wk = Nothing
        Call db.Close
    End If



    Call exportFile.Close
    Set exportFile = Nothing
    Set fso = Nothing

    Exit Sub

errorOut:
    Debug.Print "----------------"
    Debug.Print "BEGIN: Err"
    If err.Number <> 0 Then
        Msg = "Error # " & Str(err.Number) & " was generated by " _
         & err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & err.Description
        'MsgBox Msg, , "Error", err.HelpFile, err.HelpContext
        Debug.Print Msg
    End If
    Resume Close_n_exit

End Sub

无论如何可以从 PrivDBEngine 访问 application 对象?我有很多模块需要 grepping.

Is there anyway to access the application object from a PrivDBEngine? I have alot of modules that need grepping.

推荐答案

你也可以试试这个代码.它将保留项目的文件类型(.bas、.cls、.frm)记得参考/检查Microsoft Visual Basic For Applications Extensibility LibraryVBE > 工具 > 参考资料

You can also try this code. It will preserve the items' filetypes (.bas, .cls, .frm) Remember to refer to / Check the Microsoft Visual Basic For Applications Extensibility Library in VBE > Tools > References

Public Sub ExportAllCode()

    Dim c As VBComponent
    Dim Sfx As String

    For Each c In Application.VBE.VBProjects(1).VBComponents
        Select Case c.Type
            Case vbext_ct_ClassModule, vbext_ct_Document
                Sfx = ".cls"
            Case vbext_ct_MSForm
                Sfx = ".frm"
            Case vbext_ct_StdModule
                Sfx = ".bas"
            Case Else
                Sfx = ""
        End Select

        If Sfx <> "" Then
            c.Export _
                Filename:=CurrentProject.Path & "" & _
                c.Name & Sfx
        End If
    Next c

End Sub

这篇关于将 MS Access 表单和类/模块递归导出到文本文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！