通过在 SSIS 中运行 excel VBA 宏来自动化流程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25126793/
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
Automate process by running excel VBA macro in SSIS
提问by TDai
Recently, I have a project need to automate a process by combining SSIS package and excel VBA macro into one. Below are the steps:
最近，我有一个项目需要通过将 SSIS 包和 excel VBA 宏合二为一来自动化流程。以下是步骤：
I have a SSIS package exporting all the view result to multiple individual file from sql server to excel. All the files are saved in same location.
I have one excel VBA macro perform cleaning to remove all the empty sheets in each exported excel file.
I also have a excel VBA macro perform merging task to merge all the excel file into in master excel file. This master excel file contains all the result set and each result set saved on different tabs accordingly.
我有一个 SSIS 包，将所有视图结果导出到多个单独的文件，从 sql server 到 excel。所有文件都保存在同一位置。
我有一个 excel VBA 宏执行清理以删除每个导出的 excel 文件中的所有空表。
我还有一个 excel VBA 宏执行合并任务，将所有 excel 文件合并到主 excel 文件中。这个主 excel 文件包含所有结果集，每个结果集相应地保存在不同的选项卡上。
Since I am manually running step 2 and step 3, so my question is how should connect step 2 and step 3 with step 1 to combine them as one automate process.
由于我手动运行第 2 步和第 3 步，所以我的问题是如何将第 2 步和第 3 步与第 1 步连接起来，将它们组合为一个自动化过程。
Please provide me advice on how likely this can be achieved! Thanks a lot.
请向我提供有关实现这一目标的可能性的建议！非常感谢。
回答by Robert Anderson
The way to do this is to create a script task in your SSIS package.
这样做的方法是在您的 SSIS 包中创建一个脚本任务。
Then, once inside the script task, you can call the Excel interop through the C# code of the script task. e.g. you can add a reference to Microsoft.Office.Interop. Once you are using that library in your C# code of the script task, you can add some code that will call the macro. e.g.
然后，一旦进入脚本任务，您就可以通过脚本任务的 C# 代码调用 Excel 互操作。例如，您可以添加对 Microsoft.Office.Interop 的引用。在脚本任务的 C# 代码中使用该库后，您可以添加一些将调用宏的代码。例如
oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString())
//Your macro here:
oExcel.Run("Yourmacro")
Then you could write code for the other workbooks aswell, for whatever automation you need after this - e.g. you can close the workbook and open another workbook, through the Excel automation as needed.
然后，您也可以为其他工作簿编写代码，以用于此后需要的任何自动化 - 例如，您可以根据需要通过 Excel 自动化关闭工作簿并打开另一个工作簿。
