Now that we have an understanding of the fundamentals, let's create 3 automations that we will use to perform Casting.  We will need to cast to Workbooks, Worksheets and Ranges.

The first automation we will create will be called EL_F_Cast_ToWorkbook.  We need to add a parameter to the Entry Point.  In the Type column, click Browse and set the type to Microsoft.Office.Interop.Excel._Workbook.  Add an Exit Point to the automation and set its Result type to the same.  Now connect the Entry Point and Exit Point together and the automation is complete.  It should look like this.

Now we will do the same for worksheet and range.  The type for each is similar:

• For worksheet pick: Microsoft.Office.Interop.Excel._Worksheet
• For Range pick: Microsoft.Office.Interop.Excel.Range

These two automations should look like this:

Now that we have some utility automations ready, the next step will be to actually do something with Excel.  The next post will show how to find a worksheet by name.

In this post we will build an automation that will return a worksheet by name.  We will name this automation EL_F_Worksheet_GetByName. 

Before creating this automation, we need to add a Windows static method to our Toolbox.  To do this, right-click on the Toolbox and select Choose Items.  Next select the OpenSpan Static Methods tab.  On this tab choose the From Global Assembly Cache radio button and then select mscorlib, Version= Proprietary information hidden in the Assembly dropdown.  Finally, scroll down until you see String and check the checkbox next to Equals.  This will add the String.Equals method to the Toolbox which will use in this automation.

Now we will build our automation.  The Entry Point will accept an OpenSpan.Office.MicrosoftExcel object (you will pass your Excel connector to this parameter) and a string with the sheet name.  Add a Counter object to the automation and name it "sheetCnt" and add a string variable named "msg" as well.  These should both have Local scope.  In the Object Explorer, choose the excel parameter and then select the ExcelWorkbook property. 

Now build out the first line of the automation as you see below.

We now have a proxy for the Worksheets in the Workbook and a counter with the number of sheets found.  Now build out the rest of the automation.  It should look like this.

Excel collections are not zero-based, they are one-based.  This means that the first sheet would be index number 1.  So our ForLoop to iterate through the sheets must loop from index 1 to index sheets + 1 to examine every sheet.  To accomplish this, we use the Increment method of the Counter object to add 1 to the sheetCnt.  We then set that value to be the loop limit (right-click on the loop limit and choose Reset Changes to enable it).

The ForLoop outputs an index value which we pass to the GetItem method of the sheets proxy.  This returns an object which must be cast to a worksheet.  Here we use our utility automation.  Extract a proxy from the Result of the cast to get a _Worksheet object.  Now all that is required is for us to compare the name property of that object to the sheetname we passed in to the automation.  If the names match we Break the ForLoop and control passes from the Break to the Success Exit Point and we pass the _Worksheet proxy to the Result.  If the loop does not get broken control will pass to the Failed Exit Point with no _Worksheet proxy.  The Result parameter type is Microsoft.Office.Interop.Excel._Worksheet.

So how do we use this.  First we open a workbook using the Open method of the connector then we can do the following.

The next post will show how to use this to determine the Used Range of a worksheet.

In this post we will build on our previous automations to build an automation that will return the number of columns and rows that have data in a worksheet.  We will name this automation EL_F_Worksheet_GetUsedRange.

We will create an Entry Point and 2 Exit Points for this automation.  The Entry Point will accept our Excel connector and the sheet name.

The Exit Points will be called Success and Failed and will return a string named message, an integer named rows, an integer named columns and a Microsoft.Office.Interop.Excel.Range object which will be the used range.

Add 2 integer local variables, name them rowsCnt and columnCnt.  The automation will look like this.

So what we have done here is used our automation to find a worksheet by name to return a _Worksheet object.  That object has a UsedRange property.  We extract a proxy for this property and now we can examine the Rows and Columns property which have a Count property each, to find the number of rows and columns in the used range.

In this post we will build an automation that converts a number (number of columns) to an Excel column letter.  For instance, simple conversion works like this 1=A, 2=B, 3=C, etc.  This gets a little more complicated though if you have 28 columns which you need to represent as AB.  This is done by using the Modulus operator in C# which returns the remainder of a division operation. 

So, if we have 28 columns we set our dividend to 28.  We divide the dividend minus 1 by 26 to get the remainder - (28-1) / 26 returns a remainder of 1.  Using some character math we now add 1 to the character A which return B.  This will be the last letter in the address B.  Next we subtract our remainder from our dividend (28 - 26) and divide by 26 and the quotient becomes our new dividend.  We perform this operation until our dividend is 0 - each time placing the new character at the front of our string.  Therefore on our second pass we get the character A and the new result of AB.

In C# this is pretty simple - it looks like this:

We can add this as a script to a script container and use as needed.  However, how would we do this using the components available in Pega Robotics?

Step 1# - create an automation named EL_F_Column_GetLetter and the following local variables:

• dividend - type of integer
• modulo - type of integer
• columnName - type of string

Step #2 - add the code to start the automation and test for dividend greater than zero

Step#3 - add a Loop jump label, a ForLoop component and the numeric expressions shown below

Step #4 - add the rest of the logic required to finish the loop operations

Step #5 - add an Exit jump label and finish the automation

The final automation looks like this.  It will take a column count and return the column letter combination required for Excel.

With the Excel Connector, it does not appear possible to directly get access to an Excel workbook that is already open on a user's desktop. There are circumstances when this might be desirable such as when a user selects download from a website but does not want to save the Excel file so selects "Open" rather than "Save" or "Save As". [And yes, I know that the contents are saved somewhere in the user's profile but I don't want to mess with that.] The snippet of code below shows how to find all open workbooks and use the Excel Connector to interact with them.

The process begins by adding "GetActiveObject" to the Toolox. To do so, right-click on the Toolbox, select "Choose Items...", select the Pega Robotics Static Members tab, then the From Global Assembly Cache radio button, then find "mscorlib.dll, Version Proprietary information hidden", then find and expand Marshal, and put a checkmark on GetActiveObject and click OK. [Whew!]

Drag GetActiveObject onto your automation. Change the ProgID to "Excel.Application". You can use Jeff's method for casting the output of GetActiveObject as shown above, but I have had success by (temporarily) dragging the "ExcelApplication" property of the Excel Connector I am using, right-mouse clicking on it to extract a proxy, then renaming it to "_ExcelProxy1". Once I have the proxy, I disconnect it from the ExcelApplication and connect it to the GetActiveObject, and delete that ExcelApplication tile. Voila! I have cast the object as an ExcelApplication!

The workbooks property of the _ExcelProxy1 will give you a list of open workbooks. Please note that this did not work when I tried to use it on Citrix, and I haven't the resources to investigate properly. But then we know Citrix is a little weird...

Incidentally, I learned more than I expected about Listboxes. If you set the DisplayMember property to "Name" and add the actual object to the Listbox, you have the situation where if you click on one of the entries, you can get the actual object instead of just the Name in the SelectedItem property of the Listbox. Maybe that just displays my lack of education and experience with .NET, but I found it made everything easy and wanted to pass it on.

With the list of workbooks in one Listbox, the code to list the worksheets in the selected workbook in a second Listbox is straightforward and even elegant. Note that the casting as a workbook is done with the method previously shown.