SSIS脚本任务未使用外接程序运行Excel宏 [英] SSIS Script Task Not Running Excel Macro With AddIn

问题描述

我有一个调用Excel工作簿宏的SSIS脚本任务.此Excel宏使用Excel加载项(xlam)进行日志记录.当代码运行时，除非在调试期间，否则它会炸毁，我在工作簿中放置了一个断点，然后逐步执行Excel部分.如果我单步执行它，则我的过程可以通过SQL Server代理运行，然后又可以自动化运行.知道为什么我需要在断点处修复该过程吗?

I have an SSIS Script Task that calls an Excel workbook macro. This Excel macro uses an Excel AddIn (xlam) for logging. When the code runs it blows up unless during debugging, I put a breakpoint in the workbook, and then step through the Excel part. If I step through it once, my process then can run via SQL Server Agent, and in automation again. Any idea why I need to put in a breakpoint to fix the process?

这是我的示例SSIS脚本任务代码:

Here is my sample SSIS Script Task Code:

        Dim xlApp As Excel.Application = Nothing
    '
    Try
        xlApp = New Excel.Application
        xlApp.Visible = True
        xlApp.Application.DisplayAlerts = False
        xlApp.Workbooks.Open(strExcelDriverFile)
        xlApp.Run("CreateReport")
        Dts.TaskResult = ScriptResults.Success
        xlApp.Workbooks.Close()
        xlApp.Quit()
    Catch ex As Exception
        xlApp.Quit()
        Dts.TaskResult = ScriptResults.Failure
    End Try

这是我的示例Excel代码:

Here is my sample Excel Code:

Sub CreateReport
'Log using the addin
LogHWMsg Replace(Replace("Starting Macro", "'", ""), """", ""), GExecGuid, 0, 1, ThisWorkbook.Path, ThisWorkbook.Name, _
                ActiveWorkbook.Path + "\" + ActiveWorkbook.Name, Environ("UserName")
'Do some stuff...

End Sub

推荐答案

Excel Automation不会自动加载加载项.您需要通过代码专门加载它们.

Excel Automation does automatically not load add-ins. You need to specifically load them via code.

以下示例来自

The following example is from Loading Excel Add-Ins at Runtime (Written by: Jeremy Espenshade)

如果要执行以下操作，则可以使用以下选项.

The following options are available if you want to do this.

1.)Application.RegisterXLL

1.) Application.RegisterXLL

a.这是一种可以从VBA调用的方法，该方法将XLL加载到特定位置并注册XLL中包含的功能和命令.

a. This is a method which can be called from VBA which loads an XLL at a specific location and registers the functions and commands contained in the XLL.

2.)AddIns.Add

2.) AddIns.Add

a.这是一种可以从VBA调用的方法，该方法可以加载任何类型的加载项(XLL，XLA或XLAM).加载外接程序后，执行步骤3将其打开.

a. This is a method which can be called from VBA which loads any type of add-in (XLL, XLA or XLAM). After loading the add-in, perform step 3 to open it.

3.)AddIn.Installed = true

3.) AddIn.Installed = true

a.引用加载的加载项后，将AddIn.Installed = true设置为打开该加载项.

a. Once you have a reference to a loaded add-in, set AddIn.Installed = true to cause the add-in to be opened.

b.请注意，使用/automation开关启动Excel时已知的加载项已被标记为已安装"，但未打开.在这种情况下，请先将Set Installed = false设置为Installed = true

b. Note that add-ins that are known when Excel is started with the /automation switch will already be marked as "Installed", but they are not opened. In this case, Set Installed = false before setting Installed = true

Private Sub Workbook_Open()
    Dim success As Boolean
    Dim myAddIn As AddIn

    ' Load XLL
    success = Application.RegisterXLL("c:\myaddins\myxll.xll")

    ' Load and install new XLAM
    Set myAddIn = Application.AddIns.Add("c:\myaddins\myxlam.xlam")
    myAddIn.Installed = True

    ' Load known XLAM
    For Each myAddIn In AddIns
        If myAddIn.Name = "myknownaddin.xlam" Then
            myAddIn.Installed = False
            myAddIn.Installed = True
        End If
    Next
End Sub

---

OP要求我包括对他有用的技术；即激活执行Excel的用户帐户可用的加载项.

---

The OP has asked that I include the technique that worked for him; i.e. activating the add-ins that are available to the user account under which Excel is executing.

AddIns对象是Excel.Application对象的属性.当您使用Excel自动化时，Excel应用程序将不会自动加载将在交互式会话中加载的AddIns.因此，您需要使用上面演示的适当技术来根据加载项的类型加载加载项.

The AddIns object is a property of the Excel.Application object. When you are using Excel automation, the Excel application will not automatically load the AddIns that would be loaded in an interactive session. Therefore, you need to use the proper technique demonstrated above to load the add-in based on its type.

激活已知加载项的示例:

Example Activating Known Addins:

xlApp = New Excel.Application
For Each addin As Excel.AddIn In xlApp.AddIns
    If Not String.IsNullOrWhiteSpace(addin.Path) Then
        addin.Installed = True
    End If
Next

请不要让代码验证Path属性是否为空.这可能是由于卸载了外接程序引起的.

Please take not that the code verifies that the Path property is not empty. This can be cause by an add-in that was uninstalled.

Excel在维护到以前加载的加载项的链接方面也很痛苦.实际上，从列表中删除的唯一方法是使文件从原始加载的路径中不可用，并明确要求Excel一旦找不到加载项，则通过对话框窗口要求Excel将其删除.因此，可能无法访问已知"加载项.更糟的是，在无法访问的加载项上设置Installed属性不会引发错误.

Excel is also a pain about maintaining links to previously loaded add-ins. In fact the only way to remove on from the list is to make the the file unavailable from the originally loaded path and to specifically ask Excel to remove it via a dialog window once Excel can not find the add-in. Therefore, a Known add-in may not be accessible. To make this worse, setting the Installed property on an inaccessible add-in does not throw an error.

但是，如果您尝试访问可卸载加载项的任何成员，则会引发错误.

However an error will be thrown if you try to access any member of the unloadable add-in.

这篇关于SSIS脚本任务未使用外接程序运行Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！