Cannot run the macro … from SSIS

问题:

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:\VersionWithDummyData\finbalReviewTest.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.*

回答1:

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,.....