VBScript将代码添加到Excel工作簿

问题描述

我有任务向多个Excel工作簿添加一个 WorkBook_Open（）方法。我设法编写一个脚本，它添加了一个需要的代码行的模块（标记为红色），但是在打开工作簿时，我需要它在 DieseArbeitsmappe （标记为绿色）中自动启动。

I have the task to add a WorkBook_Open() method to numerous Excel Workbooks. I managed to write a script which adds a module (marked red) with the needed code lines but I need it in the DieseArbeitsmappe(marked green) to autostart when the workbook is opened.

Set xlmodule = objworkbook.VBProject.VBComponents.Add(1)
strCode = _
    "Sub WorkBook_Open()" & vbCr & _
    "   Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _
    "End Sub"
xlmodule.CodeModule.AddFromString strCode

这是我现在的代码我确定错误是在设置xlmodule的第1行，但我不能指出如何引用 DieseArbeitsmappe 。感谢您的帮助。

This is the code I have right now. I'm pretty sure that the mistake is at line 1 when setting the xlmodule but I can't figure how to reference to DieseArbeitsmappe. Thanks in advance for your help.

推荐答案

首先，您不应该使用 xlmodule 作为变量名。它已经被定义为Excel常量。我会把它称为组件。 VBComponents 集合具有一个索引器，它接受组件的序数索引或组件名称。在你的情况下，最简单的方法是使用这个名字：

First, you shouldn't use xlmodule as a variable name. It's already defined as an Excel constant. I'd call it something like component instead. The VBComponents collection has an indexer that accepts either the ordinal index of the component or the component name. In your case it would be easiest to just use the name:

Set component = ThisWorkbook.VBProject.VBComponents("DieseArbeitsmappe")
code = _
    "Sub WorkBook_Open()" & vbCr & _
    "   Application.Run ""'CommonMacro.xlsm'!Workbook_Open""" & vbCr & _
    "End Sub"
component.CodeModule.AddFromString code

请注意直接调用事件处理程序通常不是最佳做法。如果您需要重新使用该功能，您应该将其从处理程序中拉出，然后将 Workbook_Open 调用提取的 Sub 。

Note that it's usually not best practice to call an event handler directly like that. If you need to reuse the functionality, you should pull it out of the handler and then have Workbook_Open call the extracted Sub.

这篇关于VBScript将代码添加到Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！