Using the Script component
Use the Script component to create custom methods to include in Studio solutions. You can use C# to write methods. Note the following Script component requirements:
The Script component is restricted to method-level code.
A script can contain multiple methods.
All methods within a script must use the same language.
Methods can reference external assemblies. You must, however, add the references before using the references in the method syntax (see the Script References property).
You can create an alias for a namespace or use the types in a namespace for Script usages and imports. C# does not support aliases for namespaces or type names.
This example shows how to return a list of names of the worksheets in an Excel workbook. To access the worksheet and workbook objects for use in the method, the Microsoft.Office.Interop.Excel component must be added as a reference.
Add a Script component to the automation. Right-click and select the Global tab to make sure the Script methods are accessible to all automations in the project.
Browse the References property to open the Script References window.
In the Script References dialog, click the GAC button. The GAC Assemblies dialog is displayed.
Scroll to the Microsoft.Office.Interop.Excel assembly, highlight the assembly and then click OK to add the assembly for the script.
Right-click on the References folder in the Solution Explorer and select Add Reference. The Add Reference dialog is displayed.
Click the .NET tab, scroll to Microsoft.Office.Interop.Excel Component Name, and click Add. The component is displayed in the Selected projects and components grid.
Click OK to continue. Microsoft.Office.Interop.Excel is displayed in the References folder in Solution Explorer.
Double-click on the Script component. The Script Constructs window is displayed. Confirm that the language is C#.
Click the Add Method button.
Enter GetWorksheets for the method Name.
Enter _Workbook excelWkBook for the Parameters. The _Workbook type is the COM object for Excel workbooks.
Enter ArrayList as the result.
Enter the following code:
ArrayList al = new ArrayList();
//Get Sheets collection representing all the worksheets
//in the workbook excelWkBook
Sheets excelSheets = excelWkBook.Worksheets;
//Create null Worksheet object excelWorksheet
Worksheet excelWorksheet = null;
//If the Sheets collection is not empty for the input Workbook, iterate
//through the sheets, get each sheet, and add the name of the sheet
//to the arraylist al
if (excelSheets!= null)
{
for (int i = 1; i<= excelSheets.Count; i++)
{
excelWorksheet = (Worksheet)excelSheets.get_Item((object)i);
al.Add(excelWorksheet.Name.ToString());
}
}
return al;
Click the Validate button. If no error message is returned, click the Save Method button then click OK.
Add a Windows form to the project and complete it as shown below.
Add an automation and complete it as shown below:
Save, debug, and build the project.
If no build errors are encountered, enter the name and path of a workbook with multiple worksheets in the text box on the Windows form and click Get Sheets. A list of worksheet names is returned, as shown below.
