如何从另一个Excel工作簿调用表格特定的宏？ [英] How to call sheet-specific macro from one Excel workbook in another?

问题描述

我正在尝试调用另一个工作簿中的一个Excel宏。它是一个特定于工作表的宏，但Microsoft文档给出的语法和在网络上进行研究的方式只能通过工作簿访问宏。这个语法是：

  Application.Run（testworkbook.xls！macroname）
  

我需要做的是在其中还有一个工作表引用，例如：

  Application.Run（testworkbook.xls！Sheet1.macroname）
  

我已经尝试过这个和其他许多变体，包括单引号或双引号，但我总是收到不能找到该宏的消息。

编辑：有了所有的线索和许多测试，我找到了答案。您可以访问特定于表格的子页面，但是您必须使用像Sheet1这样的规范名称，则不能使用实际的页面名称。显然，其他工作簿不能访问该信息。

所以上面的格式只要你不尝试使用工作表（你可能需要单引号工作簿名称（通过连接 CHR（39）到任一端）。

解决方案

我知道你已经想到了这一点，可能是经过大量的脱毛和咖啡，但是我想：

• 给你更多细节为什么这是


• 为您提供一种方式，您可以使用
  您的工作表的名称来获取
  想要的。

首先，你想要的工作表名称与代码模块名称不一样，所以在你的VBE中，你看到代码模块的名称是Sheet1，但是如果可能具有不同的名称不同的属性，例如 MySheet1 （或者它也可能是一样的）。

为了得到它的名字，你必须做一些循环，更改安全设置等。如果这是你以后（在较小的环境中，由于安全设置问题），这里你可以作为一个例子：

1. 将您的安全设置更改为
   信任程序访问VBA
   项目。在Excel 2007中，转到Orb | Excel选项|信托中心|信任中心设置|宏
   设置然后启用信任
   访问VBA项目模型



2. 创建一个包含
   工作表的工作簿。重命名为MySheet1 。
   打开VBE（Alt + F11）和
   Sheet1（MySheet1）创建一个sub
   例程，调用它 TimesTen 和
   代码只是把 Debug.Print 10 *
10 。像这样：

     Sub TimesTen（）
    Debug.Print 10 * 10 
    End Sub 
     




3. 将文件另存为启用宏的
   文档，并将其调用为
   MacroXSLX.xlsm 。将其打开。




4. 打开一个新的Excel文档，浏览
   到它的VBE和一个新的宏
   任何地方，创建一个子称为
   测试。在该代码的正文中，
   放这个：
   。

     Sub test（）
    Dim SheetName As String 
    SheetName =MySheet1
    Dim wb As Workbook 
   设置wb =工作簿（MacroXSLX.xlsm）
   对于每个VBComp在wb.VBProject.VBComponents 
   如果VBComp.Properties.Item（Name）。Value = SheetName然后
    Application.Run（wb.Name&！& VBComp.Name&.TimesTen）
   结束如果
   下一个
    End Sub 
     




5. 按F5运行 test ，而
   应该在Immediate
   窗口中看到 100 。

您可以在＃4中看到我正在遍历所有组件（模块，类等），寻找具有名称属性，值为 MySheet1 。一旦我有这样的话，我可以得到该组件的名称，在这种情况下，这是 Sheet1 ，并使用它来构造我的字符串，将运行表格的宏在MacroXSLX.xlsm 。当你找到你想要的东西等等时，代码可以进一步清理以退出For语句。

如上所述，唯一真正的回溯到这个是安全设置部分，确保您可以对一到十台计算机进行VBAProject的编程访问，但如果您必须确保更多的设置始终设置正确，可能会导致麻烦。

I'm trying to call an Excel macro that's in another workbook. It's a sheet-specific macro, but the syntax given by Microsoft documentation and researching on the web, only gives a way to access a macro by workbook only. That syntax is:

Application.Run ("testworkbook.xls!macroname")

What I need to do is have a sheet reference in there also, something like:

Application.Run ("testworkbook.xls!Sheet1.macroname")

I've tried this and MANY other variations, including having single or double quotes in there, but I always get the message that the macro cannot be found.

Edit: With all the clues and much testing I found the answer. You can access Sheet-specific subs, but you have to use the canonical name like 'Sheet1', you can't use the actual sheetname. Apparently other workbooks don't access that information.

So the format above works as long as you don't try to use the sheetname (and you may have to single quote the workbook name (by concatenating CHR(39) to either end).

解决方案

I know you've figured this out, probably after much hair-tearing and coffee, but I wanted to:

• Give you more details on why this is
• Provide you with a way you could use your sheet's name to get what you want.

First of all, the worksheet name you are wanting is not the same thing as the code module name. So in your VBE, you see that the name of the code module is "Sheet1", but if may have a different property of Name which is different, for example, "MySheet1" (or it also may be the same).

In order to get it by name, you'll have to do some loops, change security settings, etc. If that's what you're after (this works well in smaller environments because of the security setting issue), here you go as an example:

1. Change your security settings to trust programmatic access to VBA Projects. In Excel 2007, go to Orb | Excel Options | Trust Center | Trust Center Settings | Macro Settings and then enable "Trust access to the VBA project model"

2. Create a workbook with one worksheet. Rename it "MySheet1". Open the VBE (Alt+F11) and in "Sheet1 (MySheet1)" create a sub routine, call it TimesTen and in the code just put Debug.Print 10 * 10. Like this:

   Sub TimesTen()
       Debug.Print 10 * 10
   End Sub
   

3. Save the file as an macro-enabled document and call it "MacroXSLX.xlsm". Leave it open.

4. Open a new Excel document, navigate to it's VBE and in a new macro anywhere, create a sub called Test. In the body of that code, put this: .

   Sub test()
   Dim SheetName As String
   SheetName = "MySheet1"
   Dim wb As Workbook
   Set wb = Workbooks("MacroXSLX.xlsm")
   For Each VBComp In wb.VBProject.VBComponents
       If VBComp.Properties.Item("Name").Value = SheetName Then
           Application.Run (wb.Name & "!" & VBComp.Name & ".TimesTen")
       End If
   Next
   End Sub
   

5. Press F5 to run test and you should see 100 in the Immediate window.

You can see in #4 that I'm looping through all the components (Modules, Classes, etc.) looking for the one that has a Name property that has a value of MySheet1. Once I have that, I can then get the name of that component, which in this case is Sheet1 and use that to construct my string that will run the sheet's macro in MacroXSLX.xlsm. The code can be cleaned up further to exit the For statement when you've found what you want, etc.

As mentioned above, the only real draw-back to this is the security settings piece and ensuring you have programmatic access to the VBAProject - fine on one to ten computers, but could be a hassle if you have to ensure more than that are always set correctly.

这篇关于如何从另一个Excel工作簿调用表格特定的宏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！