☰

從 VBA 開啟 Excel 時，Excel 加載項未載入

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
從 VBA 開啟 Excel 時，Excel 加載項未載入

我正在使用Excel正規表示式插件它工作得很好，唯一的問題是當 VBA 啟動 excel 時它不會加載。

我在word中有一個巨集來收集Word中的一些資料並將其複製到excel，這個啟動excel，輸出很好，但我需要重新啟動excel才能使用加載項（它在選項中啟用） ，只是沒有加載）。

我嘗試了另一個巨集：只需打開 Excel 並建立一個新工作簿，它也有相同的問題。

我只有這個第 3 方加載項，因此無法與其他加載項進行比較。

任何想法？

答案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 也是一個很好的故障排除步驟）。

啟動您的應用程序，然後對其進行 GetObject。

此外，沒有什麼理由使用諸如插件之類的插件，因為您可以執行插件正在做的事情。這是 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 集合似乎包含了“工具”->“插件”選單中列出的所有插件，並帶有一個布林值，說明是否安裝了插件。所以現在對我來說似乎有用的是循環所有插件，如果 .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

相關內容