☰

从 VBA 打开 Excel 时 Excel 加载项未加载

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
从 VBA 打开 Excel 时 Excel 加载项未加载

我正在使用 Excelreg-ex 插件并且它运行良好，唯一的问题是当通过 VBA 启动 Excel 时它不会加载。

我在 Word 中有一个宏，用于收集 Word 中的一些数据并将其复制到 Excel，这个宏启动 Excel，输出很好，但我需要重新启动 Excel 才能使用该插件（它在选项中启用，只是没有加载）。

我尝试了另一个宏：只需打开 Excel 并创建一个新的工作簿，它就会出现同样的问题。

我只有这一个第三方插件，所以无法与其他插件进行比较。

任何想法？

答案1

这就是它应该如何工作。

来自 COM 文档。

      Component Automation  
Exposing the Application Object  

 Language Filter: All Language Filter: Multiple Language Filter: Visual Basic Language Filter: C# Language Filter: C++ Language Filter: J# Language Filter: JScript  
 Visual Basic (Declaration) 
 Visual Basic (Usage) 
 C# 
 C++ 
 J# 
 JScript 

Any document-based, user-interactive applications that expose ActiveX objects should have one top-level object named the Application object. This object is initialized as the active object when an application starts.

The Application object identifies the application and provides a way for ActiveX clients to bind to and navigate the application's exposed objects. All other exposed objects are subordinate to the Application object; it is the root-level object in the object hierarchy.

The names of the Application object's members are part of the global name space, so ActiveX clients do not need to qualify them. For example, if MyApplication is the name of the Application object, a Visual Basic program can refer to a method of MyApplication as MyApplication.MyMethod or simply MyMethod. However, you should be careful not to overload the Application object with too many members because it can cause ambiguity and decrease performance. A large, complicated application with many members should be organized hierarchically, with a few generalized objects at the top, branching out into smaller, more specialized objects. 

The following chart shows how applications should expose their Application and Document objects.

Command line  Multiple-document interface application  Single-document interface application  
/Embedding 
 Expose class factories for document classes, but not for the application.

Call RegisterActiveObject for the Application object.
 Expose class factories for document class, but not for the application.

Call RegisterActiveObject for the Application object.

/Automation 
 Expose class factories for document classes.

Expose class factory for the application using RegisterClassObject.

Call RegisterActiveObject for the Application object.
 Do not expose class factory for document class.

Expose class factory for the Application object using RegisterClassObject.

Call RegisterActiveObject for the Application object.

No OLE switches 
 Expose class factories for document classes, but not for the application.

Call RegisterActiveObject for the Application object.
 Call RegisterActiveObject for the Application object. 


The call to RegisterActiveObject enters the Application object in OLE's running object table (ROT), so ActiveX clients can retrieve the active object instead of creating a new instance. Visual Basic applications can use the GetObject statement to access an existing object.

 © Microsoft Corporation. All rights reserved.

因此 /a 或 /embedding 除了裸应用程序之外不会加载任何内容（并且 /a 也是一个很好的故障排除步骤）。

启动您的应用程序，然后在其上获取对象。

此外，使用此类插件的理由很少，因为您可以执行插件所执行的确切操作。这是 vbs（因此可粘贴到 VBA 中），使用与您的插件相同的 RegEx 引擎（Word 也有自己的 RegEx 引擎，称为Use Wildcards）。设置对 的引用Microsoft VBScript Regular Expression 5.5。

Set regEx1 = New RegExp
If Instr(LCase(Arg(1)), "i") > 0 then
    regEx1.IgnoreCase = True
Else
    regEx1.IgnoreCase = False
End If 
If Instr(LCase(Arg(1)), "v") > 0 then
    IncExc = False
Else
    IncExc = True
End If 
regEx1.Global = False
regEx1.Pattern = Pttn 
Do Until Inp.AtEndOfStream
    Line=Inp.readline
    If RegEx1.Test(Line) = IncExc then
        outp.writeline Line
    End If
Loop

答案2

我已经根据 stackoverflow 中类似问题的答案解决了这个问题：https://stackoverflow.com/a/806720/4721734

我再次研究了这个问题，Application.Addins 集合似乎列出了 Tools->Addins 菜单中列出的所有插件，并有一个布尔值表示是否安装了插件。所以现在对我来说似乎有效的方法是循环遍历所有插件，如果 .Installed = true，那么我将 .Installed 设置为 False 然后再设置为 True，这似乎可以正确加载我的插件。

Function ReloadXLAddins(TheXLApp As Excel.Application) As Boolean

    Dim CurrAddin As Excel.AddIn

    For Each CurrAddin In TheXLApp.AddIns
        If CurrAddin.Installed Then
            CurrAddin.Installed = False
            CurrAddin.Installed = True
        End If
    Next CurrAddin

End Function

相关内容