无法运行宏...来自 SSIS [英] Cannot run the macro ... from SSIS

问题描述

使用

仍然是错误发生了错误

重要提示:

*尝试从 excel-->developer-->visual***-->Module1-->execute 执行时的宏，然后按预期工作.*

解决方案

ExcelObj.Run("Module1",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

这里您只是指向 "Module1" 而不是实际的宏."Module1" 是保存 "fileSave" 的地方.

试试这个

改变

ExcelObj.Run("Module1",Type.Missing,.....

到

ExcelObj.Run("fileSave",Type.Missing,.....

use the similar logic from Run an Excel Macro from SSIS

My Script task logic is as follows in SSIS,

public void Main()
        {
            // TODO: Add your code here
            string filename;//= Dts.Variables["User::filename"].Value.ToString();
            filename = @"D:VersionWithDummyDatafinbalReviewTest.xlsm";
            string S_Directory = @"D:VersionWithDummyData";
            if (S_Directory.Substring(S_Directory.Length - 1, 1) != @"")
            {
               S_Directory = S_Directory + @"";
            }

            DirectoryInfo finfo = new DirectoryInfo(S_Directory);

            if (filename.ToString().Substring(1, 2) != "~$")
            {
                try
                {
                    xls.Application ExcelObj = new xls.Application();
                    ExcelObj.DisplayAlerts = true;
                    ExcelObj.Visible = true;
                    ExcelObj.DefaultFilePath = S_Directory;

                    xls.Workbook eBook = ExcelObj.Workbooks.Open(filename.ToString(), false, false,
                        Type.Missing, "", "", true, xls.XlPlatform.xlWindows, "",
                        false, false, 0, false, true, 0);

                    foreach (xls.WorkbookConnection wc in eBook.Connections)
                    {
                        if (wc.Type.ToString() == "xlConnectionTypeODBC")
                        {
                            wc.ODBCConnection.BackgroundQuery = false;
                        }
                        else
                        {
                            wc.OLEDBConnection.BackgroundQuery = false;
                        }
                    }

                    eBook.RefreshAll();
                    eBook.Save();
                    ExcelObj.Run("Module1",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                        , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                        , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                        , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                        , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                        , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    eBook.Save();
                    ExcelObj.Workbooks.Close();
                    ExcelObj.Quit();
                }
                catch (COMException e)
                {
                    MessageBox.Show(filename.ToString() + " has an issue with error " + e.Message);
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

And the macros available in the excel(.xlsm) is as follows

Sub fileSave()
'
Dim newFileName As String, originalFileName As String, fileSaveName As String, fileNamePathSaved As String, fileNameSaved As String
Dim response As VbMsgBoxResult, currentRoute As String
'
ThisWorkbook.RefreshAll
ActiveWorkbook.Save ' save the current workbook before messing with it
Application.DisplayAlerts = False ' turns off alerts and messages
' Save file name and path into a variable
originalFileName = ActiveWorkbook.FullName ' gets the fullname with path
' originalFilePath = ActiveWorkbook.Path ' grabs the current path

Dim usingReplace As String
usingReplace = Replace(originalFileName, ".xlsm", ".xlsx")
ActiveWorkbook.SaveAs Filename:=usingReplace, FileFormat:=xlOpenXMLWorkbook
fileNameSaved = ActiveWorkbook.Name ' grabs the name of the saved file

Workbooks.Open Filename:=originalFileName 'reopens the original workbook file
Application.DisplayAlerts = True ' turns the alerts and messages back on


'provide an opportinity to clear the incident report flag
' If incidentFiled = True Then response = MsgBox("Do you want to clear the Incident Report?", vbInformation + vbOKCancel, "Incident Report Form")
If response = vbOK Then incidentFiled = False
'close the newly made file

' Workbooks(fileNameSaved).Close True ' sub terminates at this point
'
End Sub

the above macros is saved as Module1, when i try to run the package, data is getting refreshed but the macros is not getting executed

Note: Tried all the solutions suggested in the similar post

bust still the error is error occurred

Important:

*the macros when tried to execute from excel-->developer-->visual***-->Module1-->execute, then working as expected.*

解决方案

ExcelObj.Run("Module1",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Here you are just pointing towards "Module1" and not the actual macro. "Module1" is where the "fileSave" is saved.

Try this

Change

ExcelObj.Run("Module1",Type.Missing,.....

to

ExcelObj.Run("fileSave",Type.Missing,.....

这篇关于无法运行宏...来自 SSIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！