There is a dtsx package in place that runs SSRS reports using a configuration table, and then executes a macro where required on the .xls (it's a 2008 R2 server) file produced.
Each row of the configurations table contains the details of the report to be run, the macro details and an xml list of parameters and values to be passed to the macro; there is a foreach loop in the package that runs the report, adds a macro to the output file, runs the macro and moves on.
It looks as though it was designed to be generalised as much as possible, so any report and any macro could be entered in the configurations table.
This is working fine, but for all that it appears to be flexible, the actual C# in the script code that loads and runs the macro has a very rigid structure, meaning that only macros with exactly 4 parameters can be run - I would like to make it more flexible, but am struggling to work out how to give the ExcelObject.Run() command the correct (variable) number of parameters based on the number of nodes in the configuration xml.
I am not very experienced at working with C#, but it seems from reading this Microsoft and this Stackoverflow article that I can make an array of parameters and pass this... I just can't work out how. Here is the existing script:
using System;
using System.IO;
using System.Xml;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.SqlServer.Dts.Runtime;
using System.Reflection;
using VBIDE = Microsoft.Vbe.Interop;
namespace ST_b2148758d9a44ee4bc0d01a2d900ce9d.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
public void Main()
{
Variables UserVariables = null;
Dts.VariableDispenser.LockForRead("User::SaveLoc_Root");
Dts.VariableDispenser.LockForRead("User::SaveLoc_SubFolder");
Dts.VariableDispenser.LockForRead("User::SaveLoc_FileName");
Dts.VariableDispenser.LockForRead("User::SaveLoc_FileExtension");
Dts.VariableDispenser.LockForRead("User::VBA_Macro_Name");
Dts.VariableDispenser.LockForRead("User::VBA_Parameters");
Dts.VariableDispenser.LockForRead("User::VBA_Script");
Dts.VariableDispenser.GetVariables(ref UserVariables);
string SaveToLocation = null, ConstantName = null, DateFormat = null , SheetNameInCell = null;
//build the filename of the report we just made.
//@"C:\Temp\Repart.xls";
string Report_File_name = UserVariables["User::SaveLoc_Root"].Value.ToString()
+ UserVariables["User::SaveLoc_SubFolder"].Value.ToString()
+ UserVariables["User::SaveLoc_FileName"].Value.ToString()
+ UserVariables["User::SaveLoc_FileExtension"].Value.ToString();
//The macro and the macro name were stored in the original data set, so we can get those from local variables.
string Macro = UserVariables["User::VBA_Script"].Value.ToString();
string Macro_name = UserVariables["User::VBA_Macro_Name"].Value.ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(UserVariables["User::VBA_Parameters"].Value.ToString());
XmlNodeList Parameters = doc.GetElementsByTagName("Parameter");
//skip through all the parameters we might have - if more are added, this will need to be changed.
for (int i = 0; i < Parameters.Count; i++)
{
string ParamName = Parameters[i].Attributes["Name"].Value.ToString();
if (ParamName == "SaveToLocation")
{
SaveToLocation = Parameters[i].Attributes["Value"].Value.ToString();
}
else if (ParamName == "ConstantName")
{
ConstantName = Parameters[i].Attributes["Value"].Value.ToString();
}
else if (ParamName == "DateFormat")
{
DateFormat = Parameters[i].Attributes["Value"].Value.ToString();
}
else if (ParamName == "SheetNameInCell")
{
SheetNameInCell = Parameters[i].Attributes["Value"].Value.ToString();
}
}
//Get Excel ready to be opened
Excel.Application ExcelObject = default(Excel.Application);
Excel.WorkbookClass oBook = default(Excel.WorkbookClass);
Excel.Workbooks oBooks = default(Excel.Workbooks);
//get the vba module ready
VBIDE.VBComponent module = null;
//open excel in the background
ExcelObject = new Excel.Application();
ExcelObject.Visible = false;
//Open our report
oBooks = ExcelObject.Workbooks;
oBook = (Excel.WorkbookClass)oBooks.Open
(Report_File_name
,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value,Missing.Value
,Missing.Value,Missing.Value,Missing.Value,Missing.Value ,Missing.Value,Missing.Value);
//Add a module to our report and populate it with our vba macro
module = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(Macro);
//run the macro
ExcelObject.Run
( Macro_name,SaveToLocation,ConstantName,DateFormat,SheetNameInCell, Missing.Value, Missing.Value, Missing.Value,Missing.Value
,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value
,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value
,Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value);
ExcelObject.Visible = false;
ExcelObject.UserControl =false;
//oBook.Save();
ExcelObject.DisplayAlerts =false;
ExcelObject.Application.Quit();
ExcelObject =null;
}
}
}
The xml looks like this (I would like to be able to add more or fewer parameters, without the thing failing - that said, I don't mind having a maximum number of parameters):
<VBAParameters>
<Notes>The source spreadsheet is produced by the SSIS package "Reports.dtsx" which runs on SQL7. The settings here will be used to split that workbook into several files.
*SaveToLocation is where the script will save the each departments report (each sheet from the source workbook)
*ConstantName is the text that will appear in all files, along with the date and the department name
*DateFormat is the format of the date that will appear in the filename
*SheetNameInCell gives the address of the cell that in each sheet of the source spreadsheet contains the department name.
</Notes>
<Parameters>
<Parameter Name="SaveToLocation" Value="C:\Temp" />
<Parameter Name="ConstantName" Value="Post Summary" />
<Parameter Name="DateFormat" Value="yyyyMM" />
<Parameter Name="SheetNameInCell" Value="A5" />
</Parameters>
</VBAParameters>
Any guidance on how I might be able to pass the requisite number of non missing.value parameters to the macro would be very gratefully received. My efforts thus fare have been in vain. Luckily the xml reader does return the parameters in a predictable order, so the names are not necessary.
Following the pointer from @Ferdipux, I have solved this by making an array of variables that is the correct length for the purpose, with Missing.value values by default, and then inserted the actual parameters into the beginning of this array to provide the requisite number of values:
using System;
using System.IO;
using System.Xml;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.SqlServer.Dts.Runtime;
using System.Reflection;
using VBIDE = Microsoft.Vbe.Interop;
namespace ST_b2148758d9a44ee4bc0d01a2d900ce9d.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
public void Main()
{
Variables UserVariables = null;
Dts.VariableDispenser.LockForRead("User::VBA_MacroName");
Dts.VariableDispenser.LockForRead("User::VBA_Script");
Dts.VariableDispenser.LockForRead("User::VBA_Parameters");
Dts.VariableDispenser.GetVariables(ref UserVariables);
//The macro and the macro name were stored in the original data set, so we can get those from local variables.
string Macro = UserVariables["User::VBA_Script"].Value.ToString();
string Macro_name = UserVariables["User::VBA_MacroName"].Value.ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(UserVariables["User::VBA_Parameters"].Value.ToString());
XmlNodeList Parameters = doc.GetElementsByTagName (@"Parameter");
object[] AllParamArray = new object[31];
object[] MyParamArray = new object[Parameters.Count];
//Fill the array with as many missing values as there are parameters in the Run macro command
for (int i = 0; i < AllParamArray.Length; i++)
{
AllParamArray[i] = Missing.Value;
}
//get the parameters that we are actually going to use.
for (int i = 0; i < Parameters.Count; i++)
{
MyParamArray[i] = Parameters[i].Attributes["Value"].Value.ToString();
}
//the first parameter is always the macro name
AllParamArray[0] = Macro_name;
//after that, we can insert our list of all the parameters we need into the list of all the parameters Excel needs
MyParamArray.CopyTo(AllParamArray, 1);
//Get Excel ready to be opened
Excel.Application ExcelObject = default(Excel.Application);
Excel.WorkbookClass oBook = default(Excel.WorkbookClass);
Excel.Workbooks oBooks = default(Excel.Workbooks);
//get the vba module ready
VBIDE.VBComponent module = null;
//open excel in the background
ExcelObject = new Excel.Application();
ExcelObject.Visible = false;
ExcelObject.DisplayAlerts = false;
//Open our report
oBooks = ExcelObject.Workbooks;
oBook = (Excel.WorkbookClass)oBooks.Add(Missing.Value);
//Add a module to our report and populate it with our vba macro
module = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(Macro);
ExcelObject.Run
(AllParamArray[0], AllParamArray[1], AllParamArray[2], AllParamArray[3], AllParamArray[4], AllParamArray[5], AllParamArray[6], AllParamArray[7], AllParamArray[8],
AllParamArray[9], AllParamArray[10], AllParamArray[11], AllParamArray[12], AllParamArray[13], AllParamArray[14], AllParamArray[15], AllParamArray[16], AllParamArray[17],
AllParamArray[18], AllParamArray[19], AllParamArray[20], AllParamArray[21], AllParamArray[22], AllParamArray[23], AllParamArray[24], AllParamArray[25], AllParamArray[26],
AllParamArray[27], AllParamArray[28], AllParamArray[29], AllParamArray[30]);
oBook.Close(false,Missing.Value,Missing.Value);
ExcelObject.Application.Quit();
ExcelObject =null;
}
}
}
This is not exactly the solution suggested in the previous answer, but is based on the suggestions made there and the fact that the missing values are not optional.