vba 以编程方式实例化 Excel 时加载插件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/213375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loading addins when Excel is instantiated programmatically
提问by Jon Fournier
I am trying to create a new instance of Excel using VBA using:
我正在尝试使用 VBA 创建一个新的 Excel 实例：
Set XlApp = New Excel.Application
The problem is that this new instance of Excel doesn't load all the addins that load when I open Excel normally...Is there anything in the Excel Application object for loading in all the user-specified addins?
问题是，这个新的 Excel 实例没有加载我正常打开 Excel 时加载的所有加载项...... Excel Application 对象中是否有任何内容用于加载所有用户指定的加载项？
I'm not trying to load a specific add-in, but rather make the new Excel application behave as though the user opened it themself, so I'm really looking for a list of all the user-selected add-ins that usually load when opening Excel.
我不是要加载特定的加载项，而是让新 Excel 应用程序的行为就像用户自己打开它一样，所以我真的在寻找通常加载的所有用户选择的加载项的列表打开Excel时。
回答by Jon Fournier
I looked into this problem again, and the Application.Addins collection seems to have all the addins listed in the Tools->Addins menu, with a boolean value stating whether or not an addin is installed. So what seems to work for me now is to loop through all addins and if .Installed = true then I set .Installed to False and back to True, and that seems to properly load my addins.
我再次查看了这个问题，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
回答by Mike Rosenblum
Using CreateObject("Excel.Application")would have the same result as using New Excel.Application, unfortunately.
使用CreateObject("Excel.Application")将有相同的结果作为使用New Excel.Application，很遗憾。
You will have to load the Addins that you need individually by file path & name using the Application.Addins.Add(string fileName)method.
您必须使用该Application.Addins.Add(string fileName)方法通过文件路径和名称单独加载您需要的插件。
回答by Ben Brandt
I'm leaving this answer here for anyone else who ran into this problem, but using JavaScript.
我将这个答案留给其他遇到这个问题但使用 JavaScript 的人。
A little background... In my company we have a 3rd party web app that used JavaScript to launch Excel and generate a spreadsheet on the fly. We also have an Excel add-in that overrides the behavior of the Save button. The add-in gives you the option of saving the file locally or in our online document management system.
一点背景... 在我的公司，我们有一个 3rd 方网络应用程序，它使用 JavaScript 来启动 Excel 并动态生成电子表格。我们还有一个 Excel 加载项，它会覆盖“保存”按钮的行为。该插件为您提供了将文件保存在本地或我们的在线文档管理系统中的选项。
After we upgraded to Windows 7 and Office 2010, we noticed a problem with our spreadsheet-generating web app. When JavaScript generated a spreadsheet in Excel, suddenly the Save button no longer worked. You would click save and nothing happened.
升级到 Windows 7 和 Office 2010 后，我们注意到生成电子表格的 Web 应用程序存在问题。当 JavaScript 在 Excel 中生成电子表格时，突然“保存”按钮不再起作用。你会点击保存，什么也没发生。
Using the other answers here I was able to construct a solution in JavaScript. Essentially we would create the Excel Application object in memory, then reload a specific add-in to get our save button behavior back. Here's a simplified version of our fix:
使用这里的其他答案，我能够用 JavaScript 构建一个解决方案。本质上，我们将在内存中创建 Excel 应用程序对象，然后重新加载特定的加载项以恢复我们的保存按钮行为。这是我们修复的简化版本：
function GenerateSpreadsheet()
{
var ExcelApp = getExcel();
if (ExcelApp == null){ return; }
reloadAddIn(ExcelApp);
ExcelApp.WorkBooks.Add;
ExcelApp.Visible = true;
sheet = ExcelApp.ActiveSheet;
var now = new Date();
ExcelApp.Cells(1,1).value = 'This is an auto-generated spreadsheet, created using Javascript and ActiveX in Internet Explorer';
ExcelApp.ActiveSheet.Columns("A:IV").EntireColumn.AutoFit;
ExcelApp.ActiveSheet.Rows("1:65536").EntireRow.AutoFit;
ExcelApp.ActiveSheet.Range("A1").Select;
ExcelApp = null;
}
function getExcel() {
try {
return new ActiveXObject("Excel.Application");
} catch(e) {
alert("Unable to open Excel. Please check your security settings.");
return null;
}
}
function reloadAddIn(ExcelApp) {
// Fixes problem with save button not working in Excel,
// by reloading the add-in responsible for the custom save button behavior
try {
ExcelApp.AddIns2.Item("AddInName").Installed = false;
ExcelApp.AddIns2.Item("AddInName").Installed = true;
} catch (e) { }
}
回答by Ken Paul
Try:
尝试：
Set XlApp = CreateObject("Excel.Application")
Set XlApp = CreateObject("Excel.Application")
