excel - 在access vba中调用excel函数

Question

我在excel中有一个需要从 Access 中调用的子程序。
Excel vba

 Public Function testme(value As String) As String

 Dim xlpath As String
 Dim concate As String

 xlpath=ActiveWorkbook.Path
 value = ActiveWorkbook.Name
 concate = xlpath & "\" & value     
 Let testme = concate

 End Function

我需要在其中一种 Access 方法中调用上述方法。我该如何调用它。

 Sub Connect1()
 Dim xlApp As Variant
'Set xlApp = CreateObject("Excel.Application")
'this will launch a blank copy of excel; you'll have to load workbooks
'xlApp.Visible = True

Set xlApp = GetObject(, "Excel.Application")
    Let ans = xlApp.Application.Run("MyXLVBAProject.MyXLVBAModule.testme", 400)
'here ans has the string "500"
End Sub

Answer 1

您可能希望从 Excel 的对象模型中使用 Application.Run。您向它传递一个字符串，例如“QuickRDA.JavaCallBacks.GetQuickTab”作为宏名称，其中 QuickRDA 是 Excel VBA 项目的名称，JavaCallBacks 是该 VBA 项目中 VBA 模块的名称，GetQuickTab 是该 VBA 模块中的函数。

在 Access

Sub Connect()    
    Dim xlApp As Variant
    Set xlApp = GetObject(, "Excel.Application")
    'this will connect to an already open copy of excel, a bit easier for quick & dirty testing
    Let ans = xlApp.Application.Run("MyXLVBAProject.MyXLVBAModule.testme")
End Sub

在 Excel 中

Public Function testme() As String
    Dim xlpath As String
    Dim concate As String
    Dim value as String
    xlpath = ActiveWorkbook.Path
    value = ActiveWorkbook.Name
    concate = xlpath & "\" & value     
    Let testme = concate
End Function

——或者简单地说——

Public Function testme() As String
    Let testme = ActiveWorkbook.FullName
End Function

请记住，在 Excel 中，函数 testme 应放在名为 MyXLVBAModule 的模块中，并且包含该模块的项目应称为 MyXLVBAProject。