从VBA中的Access宏运行Excel宏 [英] Run Excel Macro from Access macro in VBA

问题描述

我正在尝试从Access 2016运行一个excel宏.这里有很多示例，但是它们都已经过时了.我收到的确切错误代码是运行时错误"1004":无法运行宏"TestScript()".该宏可能在此工作簿中不可用，或者可能显示了所有宏.我只是在进行一些简单的测试.我确保在excel中启用了宏.excel代码如下

 公共子TestScript()MsgBox成功"结束子真正简单的测试.Access正在打开excel电子表格，但是它在那里停止并显示错误代码.我的访问代码非常简单，如下所示.我还注意到代码在哪里停止.虽然我是VBA的新手，但我已经对此进行了大量研究.我正在测试尽可能简单的代码.任何帮助都将受到欢迎.期权比较数据库函数runExcelmacro()Dim XL作为对象设置XL = CreateObject("Excel.Application")带XL'关闭警告.Visible = False.displayalerts =假'工作簿路径，例如"C:\ Computer \ WorkBook.xlsx".Workbooks.打开"C:\ DATABASE \ BLQ-10 \ Import Database BLQ 10 \ NTIRAINSTALLTO.xlsm"'在Excel GetWorkbook中运行宏.运行TestScript'代码到此为止！'关闭工作簿.ActiveWorkbook.Close(正确).相当结束于设置XL =无效结束功能公共子runMacroSub()调用runExcelmacro("C:\ DATABASE \ BLQ-10 \ Import Database BLQ 10 \ NTIRAINSTALLTO.xlsm"，"TestScript")结束子 

解决方案

我猜想OP没有将 Testscript 的代码放入额外的模块中.而是将代码放入工作簿或工作表的类模块中.在后一种情况下，您必须在脚本名称之前添加工作簿或工作表名称.

要么是`

 .运行"ThisWorkbook.TestScript" 

，或者如果它在 Sheet1 中((代号！)

 .运行"Sheet1.TestScript" 

不要忘记引号！

PS 如果将 testscript 放入模块并添加引号，则上述OP的代码将起作用.

 .运行"TestScript" 

此处描述如何创建模块并添加代码

I am trying to run an excel macro from access 2016. This has a lot of examples out their however they are all old. The exact error code I receive is run-time error '1004': cannot run the macro "TestScript()". The macro may not be available in this workbook or all macros may be displayed. I am only running something easy for the test. I have ensured that macros is enabled in excel. the excel code is as follows

    Public Sub TestScript()

       MsgBox "IT WORKED"


    End Sub

Real simple for the test. Access is opening the excel spreadsheet however it stops there with an error code.
My Access code is very simple and is below. I have also noted where the code stops. While I am new at VBA I have done a lot of research in this. I am testing as simple code as I could figure out. Any help would be welcomed.


Option Compare Database


Function runExcelmacro()

    Dim XL As Object

    Set XL = CreateObject("Excel.Application")

    With XL

    'Turn Off warnings
    .Visible = False
    .displayalerts = False
    'WorkBook path such as "C:\Computer\WorkBook.xlsx"


    .Workbooks.Open "C:\DATABASE\BLQ-10\Import Database BLQ 10\NTIRAINSTALLTO.xlsm"
    'Run the Macro in excel getworkbook

    .Run TestScript 'Code stops here!

    'Close Workbook

    .ActiveWorkbook.Close (True)
    .Quite
    End With
    Set XL = Nothing

End Function


Public Sub runMacroSub()
    Call runExcelmacro("C:\DATABASE\BLQ-10\Import Database BLQ 10\NTIRAINSTALLTO.xlsm", "TestScript")
End Sub

解决方案

I guess OP did not put the code of Testscript in an extra module. Instead the code was put into the class module of the workbook or a worksheet. In the latter case you have to add the workbook or worksheet name in front of the name of the script.

Either it is `

.Run "ThisWorkbook.TestScript"

or in case it is in Sheet1 (codename of the sheet!)

.Run "Sheet1.TestScript"

Do not forget the quotation marks!

PS The OP's code above is working if you put testscript into a module and add quotation marks.

.Run "TestScript"

Here is a description how to create a module and add code

这篇关于从VBA中的Access宏运行Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！