从Excel连接到Access Vba对象模型-Java 学习之路

我在这个打印屏幕上看不到任何在Microsoft Access中启用Vba对象模型的选项：

enter image description here

从Excel我可以在相同的选项对话窗口中看到：

enter image description here

我知道以下工作正常使用Access vba对象模型

Public strAllCode As String

Public Sub StringAllLines()

    Dim accObj As AccessObject  'Each module/form/report.
    Dim bWasOpen As Boolean     'Flag to leave form/report open if it was open.
    Dim strDoc As String        'Name of each form/report

    'Stand-alone modules.
    For Each accObj In CurrentProject.AllModules
        Call GetModuleLines(accObj.Name, True)
    Next

    'Modules behind forms.
    For Each accObj In CurrentProject.AllForms
        strDoc = accObj.Name
    bWasOpen = accObj.IsLoaded

    If Not bWasOpen Then
        DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
    End If

    If Forms(strDoc).HasModule Then
        Call GetModuleLines("Form_" & accObj.Name, False)
    End If

    If Not bWasOpen Then
        DoCmd.Close acForm, strDoc, acSaveNo
    End If

    Next

    'Modules behind reports.
    For Each accObj In CurrentProject.AllReports
    strDoc = accObj.Name
    bWasOpen = accObj.IsLoaded

    If Not bWasOpen Then
    'In Access 2000, remove the ", WindowMode:=acHidden" from the next line.
        DoCmd.OpenReport strDoc, acDesign, WindowMode:=acHidden
    End If

    If Reports(strDoc).HasModule Then
       Call GetModuleLines("Report_" & accObj.Name, False)
    End If

    If Not bWasOpen Then
       DoCmd.Close acReport, strDoc, acSaveNo
    End If

    Next

    Dim intFile As Integer

    '*** Set to next free open number ***
    intFile = FreeFile()
    Open "c:\temp\AllCode.txt" For Output As #intFile
    Print #intFile, strAllCode
    Close #intFile

End Sub

Private Function GetModuleLines(strModule As String, bIsStandAlone As Boolean)

    Dim bWasOpen As Boolean     'Flag applies to standalone modules only.
    Dim lngLineNo As Long

    If bIsStandAlone Then
        bWasOpen = CurrentProject.AllModules(strModule).IsLoaded
    End If

    If Not bWasOpen Then
        DoCmd.OpenModule strModule
    End If

    strAllCode = strAllCode & "**********" & vbCrLf
    strAllCode = strAllCode & strModule & vbCrLf
    strAllCode = strAllCode & "**********" & vbCrLf

    For lngLineNo = 1 To Modules(strModule).CountOfLines
    strAllCode = strAllCode & Right("     " & lngLineNo, 5) & ": " & Modules(strModule).Lines(lngLineNo, 1) & vbCrLf
    Next

    strAllCode = strAllCode & vbCrLf & vbCrLf & vbCrLf

    If Not bWasOpen Then
        On Error Resume Next
        DoCmd.Close acModule, strModule, acSaveNo
    End If

    Debug.Print strModule & " complete"

    DoEvents

End Function

以上只是一个代码示例，它允许将所有模块和对象名称导出到Access中的文本文件中 .

我想从Excel中做到这一点，换句话说，Excel中的vba宏打开Access数据库并处理每个对象名称和类型（表，表单，查询，报告，vba模块......）到文本文件中然后关闭数据库 . 可以这样做吗？

发布的代码为reference

3 回答

0
你可以只编写你知道在访问中工作的代码并从excel运行宏吗？

不是很有活力，但会满足你的目的
```
Set oAcc = CreateObject("Access.Application")

oacc.opencurrentdatabase "C:\yourdb.accdb", ,"password"
oacc.Run "Your_Macro"
```
回复于 2024-05-05T12:03:37+08:00
1

在Excel VBE中添加Microsoft Access XX.X对象参考库将公开Access中可以从Excel中调用的方法，参数和函数/子 .

添加引用后，添加Access.Application类型的对象，然后使用Set命令将其设置为等于Access.Application对象的新实例 .

然后，您可以将变量绑定到数据集，查询/表定义，并且几乎可以在Access中执行任何操作 . 这被称为早期绑定 .

或者，您可以创建一个未绑定的对象变量（Dim foo As Object），然后将其设置为等于对象引用（Set foo = New Access.Application） .

您可以像以前一样创建和操作相同的结构，但是会丢失智能感知功能 . 这被称为后期绑定 .

回复于 2024-05-05T12:03:37+08:00

好吧，在我提出要求之前我很懒，但是从上面首次发布的代码中进行一些调整后，可以全面控制访问vba对象模型 . 请参阅下面的代码我的代码（第一次尝试）我想出了它的工作原理：

Public strAllCode As String
Public appAccess As Object

Public Sub StringAllLines()

    'create new access object
    Set appAccess = CreateObject("Access.Application")
    'open the acces project
    Call appAccess.OpenCurrentDatabase( _
    "D:\ShF3\vba_list_module_proj\access northw modules _4_ok\Northwind 2007z.accdb")
    appAccess.Visible = False

    Dim accObj As Object  'Each module/form/report.
    Dim bWasOpen As Boolean     'Flag to leave form/report open if it was open.
    Dim strDoc As String        'Name of each form/report
    'Stand-alone modules.
    For Each accObj In appAccess.CurrentProject.AllModules
        Call GetModuleLines(accObj.Name, True)
    Next
    'Modules behind forms.
    For Each accObj In appAccess.CurrentProject.AllForms
        strDoc = accObj.Name
        bWasOpen = accObj.IsLoaded
        If Not bWasOpen Then
            'DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
        End If

    Next

    'Modules behind reports.
    Dim intFile As Integer
    '*** Set to next free open number ***
    intFile = FreeFile()
    Open "c:\temp\AllCode.txt" For Output As #intFile
    Print #intFile, strAllCode
    Close #intFile

End Sub


Private Function GetModuleLines(strModule As String, bIsStandAlone As Boolean)

    Dim bWasOpen As Boolean     'Flag applies to standalone modules only.
    Dim lngLineNo As Long
    If bIsStandAlone Then
        bWasOpen = appAccess.CurrentProject.AllModules(strModule).IsLoaded
    End If
    If Not bWasOpen Then
        appAccess.DoCmd.OpenModule strModule
    End If
    strAllCode = strAllCode & "**********" & vbCrLf
    strAllCode = strAllCode & strModule & vbCrLf
    strAllCode = strAllCode & "**********" & vbCrLf

    For lngLineNo = 1 To appAccess.Modules(strModule).CountOfLines
        strAllCode = strAllCode & Right("     " & lngLineNo, 5) & ": " & appAccess.Modules(strModule).Lines(lngLineNo, 1) & vbCrLf
    Next

    strAllCode = strAllCode & vbCrLf & vbCrLf & vbCrLf

    If Not bWasOpen Then
        On Error Resume Next
        'appAccess.DoCmd.Close acModule, strModule, acSaveNo
    End If

    Debug.Print strModule & " complete"
    DoEvents

End Function

我只需要正确关闭仍然打开的访问数据库，并对文本文件中报告的信息进行一些调整 . 最后，我不需要将文本文件作为中间步骤，而是将所有内容都放在excel中 . 帕斯卡尔

回复于 2024-05-05T12:03:37+08:00

从Excel连接到Access Vba对象模型

3 回答

相关问题