在Access中使用嵌入式Excel工作表运行宏 [英] Run a macro with embedded Excel sheet in Access

问题描述

我的问题很简单，但到目前为止我还没有找到解决方法...

My problem is quite simple but I haven't found a solution so far...

我在Access中创建了一个名为Form1的表单. 在此表单中，我插入了一个未绑定对象框架"，这是一个启用了宏的新Excel工作表. 在Excel工作表中，我创建了一个可以称为"ExcelMacro"的宏.

I created a form in Access called Form1. In this form, I inserted an "Unbound Object Frame", which is a new macro-enabled Excel worksheet. In the Excel sheet, I create a macro we can call "ExcelMacro".

我想知道如何通过Access在此Excel工作表中运行宏，例如在我的Access宏中，运行"ExcelMacro"

I want to know how to run macros in this Excel sheet from Access, e.g. in my Access macro, run "ExcelMacro"

我不想将Excel工作表链接到外部Excel工作簿.

I do NOT want to link my Excel sheet to an external Excel workbook.

你有什么主意吗?

非常感谢您的帮助！

如果您需要一些其他信息:

In case you need some additional information:

未绑定对象框架的名称:xlObject

Name of Unbound Object Frame: xlObject

OLE类:Microsoft Excel启用宏12

OLE Class: Microsoft Excel Macro-Enabled 12

类:Excel.SheetMacroEnabled.12

Class: Excel.SheetMacroEnabled.12

工作表名称:Sheet1

Sheet name: Sheet1

推荐答案

您可以在Access中使用以下内容:

You can use the following in Access:

Public Sub RunExcelMacro()
    Dim excelApp As Object
    Set excelApp = GetObject(, "Excel.Application")
    excelApp.Run "HelloWorld"
End Sub

其中"HelloWorld"是Excel宏的名称.

Where "HelloWorld" is the name of the Excel Macro.

GetObject(, "Excel.Application")获取最新打开的Excel应用程序.这需要是运行嵌入式工作表的Excel应用程序.如果是另一个，它将失败.

GetObject(, "Excel.Application") gets the latest opened Excel application. This needs to be the Excel application that is running your embedded worksheet. If it's another, it will fail.

此外，需要打开工作表，否则它将失败(您可以在运行此工作表之前添加以下代码段之一来打开它).

Also, the worksheet needs to be open, else it will fail (you can add either of the following code segments to open it before running this).

Me.MyOLEUnbound.Verb = 0 'vbOLEPrimary
Me.MyOLEUnbound.Action = 7 

或

Me.MyOLEUnbound.AutoActivate = 1 'vbOLEActivateGetFocus
Me.MyOLEUnbound.SetFocus

为确保没有其他Excel实例正在运行，并在可能的情况下退出它们(请注意:这些实例使用运行时错误和错误处理程序)

To make sure there are no other instances of Excel running, and possibly quit them if they are (note: these make use of runtime errors and error handlers)

Public Function IsExcelRunning() As Boolean
    IsExcelRunning = True
    On Error GoTo ReturnFalse:
    Dim obj As Object
    Set obj = GetObject(, "Excel.Application")
    Exit Function
ReturnFalse:
    IsExcelRunning = False
End Function

Public Sub CloseAllExcel()
    Dim obj As Object
    On Error GoTo ExitSub
    Dim i As Integer
    'There shouldn't be more than 10000 running Excel applications
    'Can use While True too, but small risk of infinite loop
    For i = 0 To 10000
        Set obj = GetObject(, "Excel.Application")
        obj.Quit
    Next i
ExitSub:
End Sub

这篇关于在Access中使用嵌入式Excel工作表运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！