Pega Robotics Studio 8: interrogating Office (Excel) menu ribbon
Is it possible to interrogate and hence operate within automations the controls in the ribbon of Excel or any other Microsoft Office 2013 application?
If not, is there any other way, apart from SendKey, of achieving this - e.g. using the Microsoft Office Connectors?
If not, is there a way of using SendKey with HideApplicationAtRuntime set to true?
If not, is there a way of running automations on screen locked terminals with HideApplicationAtRuntime set to false?
Our client has very many use cases which distill to this requirement - e.g. they need to automatically save attachments from emails in Outlook as well, and they need to both read from and write to Excel documents with embedded VBA scripts which need to be executed as part of automations.
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Pegasystems Inc.
JP
In order to close this: the short answer is "No, it's not possible to interrogate the ribbon", and any function not supported by the (Excel, etc.) connectors have to be avoided.
That said, most of the requirements I have managed to find a way to handle using the connector. I managed to read from a password protected file without opening the file using the automation attached (the Workbook property of the Excel connector has to be set to a dummy, non-password protected Excel file for this to work - and the password protected Excel file is specified full path in the "Open" component. ReturnTypeMissing is a simple script that calls "return Type.Missing"; casting to objects is explained in Jeff's post. I later noticed a similar post here:
https://collaborate.pega.com/discussion/expression-builder-not-visible-ie7-using-pega-7-pe
Pegasystems Inc.
US
Michael,
For saving Outlook attachments - look at this post. As to your other questions - are you really asking can you run macros? The Excel connector has a RunMacro command which works nicely. As far as the questions on the Ribbon, that is not likely to work (I have not tried but the control is not a supported control). Working with a locked machine is usually problematic but please setup a simple test solution and let us know the results. We encourage an unlocked machine as an automation is functioning as a human would and the machine is unlocked for the human. If you are working with the Excel object model and not the interface I suspect some functions will work in a locked environment, though.
Jeff
Pegasystems Inc.
JP
Jeff, thanks as always for the promptness.
The user has a multitude of use cases involving updating and/or reading Excel documents, and their macros are more likely to be embedded to react to changes and reflect updates elsewhere in the workbook or in even in other workbooks, than to be executed directly from Robotics. Furthermore, the updates aren't only from entering values into cells, but also from the ribbon functions, especially from functions such as sort and filter from the data ribbon. They are discovering other requirements but the only other one I can think of right now is to lock/unlock sheets. Without connectors the only way I can think of implementing these is with SendKey and the screensaver automatically kicking in after a few minutes is going to remain mandatory in the insurance industry.
If you don't have any other ideas and something can't be worked out around the screensaver and SendKey I suspect the gaps in Robotics' functionality can be filled in with yet more macros as they are quite positive in adapting to us if necessary.
Pegasystems Inc.
US
Lock and Unlock sheets, etc. can be done using the connector. So can sort and filter. If their macros trigger from the actual event instead of the ribbon action it should work. Obviously, this needs to be worked through one use case at a time and we can't address all of them here.
Pegasystems Inc.
US
Take a look at my post on Excel. You need to get a _Worksheet object. The _Worksheet object has a Protect and an Unprotect method which you can call. The way to call this is described in MSDN.
To apply a filter to a sheet, you will need to acquire a Range object. The Excel post shows how to get the UsedRange from a worksheet. A Range has an AutoFilter method which will do what you need. Here are the options found in MSDN. Sorting a range can also be done, this one is a little complicated though. Here is the MSDN reference on it.
So basically, you acquire the Workbook, Worksheet or Range objects from the Excel connector. Each one of these objects has properties and methods you can use to perform any function you can do in Excel.
Pegasystems Inc.
JP
Sorry it seems that our customer wanted to lock/unlock workbooks, not worksheets. I managed to lock the workbook quite simply this way:
Unlocking it is much trickier because we first have to open the file which is password protected. I built this:
However, when I set the "Workbook"property of the Excel connector to the workbook I want to open (which is also the parameter of the "Open" connector above, it attempts to open the workbook on load, so before the automation even reaches the Open connector, and prompts for the password.
Sorry it seems that our customer wanted to lock/unlock workbooks, not worksheets. I managed to lock the workbook quite simply this way:
Unlocking it is much trickier because we first have to open the file which is password protected. I built this:
However, when I set the "Workbook"property of the Excel connector to the workbook I want to open (which is also the parameter of the "Open" connector above, it attempts to open the workbook on load, so before the automation even reaches the Open connector, and prompts for the password.
However, if I clear this field, it gives me the error "GetWorkingInstance() could not get instance from Type Proxy. InstanceName: _ApplicationProxy1":
Does anyone know to open the password protected sheet? Once I open it I can easily set the password back to null to disable password protection.
Pegasystems Inc.
JP
We now have another customer who needs to open a workbook which prompts "Do you want to open the file as readonly", for who we use the same Workbooks.Open() function with ReadOnly set to true, and the behaviour is the same:
1. If we set the Excel connector Workbook property to the same filename we pass to the Workbooks.Open() function, Robotics attempts to open the file before the automation reaches the Open() function and the ReadOnly prompt appears (and must be manually clicked).
2. If we set the Excel connector Workbook property to null, we get the error "GetWorkingInstance() could not get instance from TypeProxy. InstanceName: _ApplicationProxy1" at the _ApplicationProxy1.Workbooks connector just before Open().
3. If we set the Excel connector to a dummy Excel file which doesn't prompt on opening, both the dummy Excel file opens successfully on start, then the target Excel file opens successfully with the Open() call; however, when we later try to ExportData from the Excel connector it attempts to export from the dummy file, not the target file.
4. If we change the property of the Excel connector to the target file *after* the Open() command succeeds, it attempts to open the target file again on that property set connector despite the fact that it is already open, and again pops the prompt which must be manually handled.
We now have another customer who needs to open a workbook which prompts "Do you want to open the file as readonly", for who we use the same Workbooks.Open() function with ReadOnly set to true, and the behaviour is the same:
1. If we set the Excel connector Workbook property to the same filename we pass to the Workbooks.Open() function, Robotics attempts to open the file before the automation reaches the Open() function and the ReadOnly prompt appears (and must be manually clicked).
2. If we set the Excel connector Workbook property to null, we get the error "GetWorkingInstance() could not get instance from TypeProxy. InstanceName: _ApplicationProxy1" at the _ApplicationProxy1.Workbooks connector just before Open().
3. If we set the Excel connector to a dummy Excel file which doesn't prompt on opening, both the dummy Excel file opens successfully on start, then the target Excel file opens successfully with the Open() call; however, when we later try to ExportData from the Excel connector it attempts to export from the dummy file, not the target file.
4. If we change the property of the Excel connector to the target file *after* the Open() command succeeds, it attempts to open the target file again on that property set connector despite the fact that it is already open, and again pops the prompt which must be manually handled.
It seems so close to working, but won't - we need a way to set the Workbooks in the Excel connector without it attempting to open the file until we call Open().
Accepted Solution
Pegasystems Inc.
JP
In order to close this: the short answer is "No, it's not possible to interrogate the ribbon", and any function not supported by the (Excel, etc.) connectors have to be avoided.
That said, most of the requirements I have managed to find a way to handle using the connector. I managed to read from a password protected file without opening the file using the automation attached (the Workbook property of the Excel connector has to be set to a dummy, non-password protected Excel file for this to work - and the password protected Excel file is specified full path in the "Open" component. ReturnTypeMissing is a simple script that calls "return Type.Missing"; casting to objects is explained in Jeff's post. I later noticed a similar post here:
https://collaborate.pega.com/discussion/expression-builder-not-visible-ie7-using-pega-7-pe
