vba 在access vba中调用excel函数

Call excel function in access vba

提问by vuyy1182

I've a sub in excel that needs to be called from access. Excel vba

我有一个需要从访问中调用的 excel 子程序。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

i need to call above method in one of the access method.How do i call it.

我需要在访问方法之一中调用上述方法。我如何调用它。

 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

采纳答案by Erik Eidt

You'll probably want to use Application.Run from Excel's object model. You pass it a string such as "QuickRDA.JavaCallBacks.GetQuickTab" for the macro name, where QuickRDA is the name of the Excel VBA project, JavaCallBacks is the name of the VBA module in that VBA project, and GetQuickTab is the name of the function in that VBA module.

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

In 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

In Excel

在 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

-or simply-

——或者干脆——

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

Remember that in Excel the function testme should be put in a module whose name is MyXLVBAModule, and that the project containing the module should be called MyXLVBAProject.

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

回答by ASH

So, you want to trigger an Excel function from Access, or run an Excel subroutine from Access?

那么，您想从 Access 触发 Excel 函数，还是从 Access 运行 Excel 子例程？

To run a function, you can do something like this.

要运行一个函数，你可以做这样的事情。

  Public Function FV(dblRate As Double, intNper As Integer, _ 
                  dblPmt As Double, dblPv As Double, _ 
                  intType As Integer) As Double
     Dim xl As Object
     Set xl = CreateObject("Excel.Application")
     FV = xl.WorksheetFunction.FV(dblRate, intNper, dblPmt, dblPv, intType)
     Set xl = Nothing
 End Function

To run an Excel subroutine from Access, you can do the following.

要从 Access 运行 Excel 子例程，您可以执行以下操作。

Sub RunExcelMacro()
Dim xl As Object
'Step 1:  Start Excel, then open the target workbook.
   Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("C:\Book1.xlsm")
'Step 2:  Make Excel visible
   xl.Visible = True
'Step 3:  Run the target macro
   xl.Run "MyMacro"
'Step 4:  Close and save the workbook, then close Excel
   xl.ActiveWorkbook.Close (True)
    xl.Quit
'Step 5:  Memory Clean up.
   Set xl = Nothing 
End Sub