Protect/Unprotect password protect excel worksheet
I am trying to automate a password protected excel workbook. I was able open a password protected workbook with Open Method (4 parameters) but after that worksheet is also protected with a Form button in worksheet itself. I need to unprotect the worksheet as well. There is a Macro assigned to that button which I was able to run with RunMacro method to open “Enter Password Form” but after that the whole automation stops until I manually type the password and click “OK” or “Cancel”. I thought I was able to type password using SendKeys method but the automation doesn’t move to the next step so that I can use SendKeys. Also, I was not able to interrogate the textbox in the form just part of the form only (I am aware of limitations of interrogating an excel workbook). Please find the attachments on what I have tried so far. Let me know if you have any solution or suggestion to my problem.
***Edited by Moderator: Pallavi to update platform capability tags***
***Edited by Moderator Marissa to update SR Details***
Accepted Solution
Pegasystems Inc.
US
Following solution design was implemented to help Dipesh with password protected workbook and worksheet.
The solution opens a password protected and unprotects the active excel sheet of that workbook.
Please note the new Open Microsoft Excel connector method used in the solution is only available in Robotics Version 8.0.2010 and later.
Coforge BPM
US
I cannot sendkeys (both control sendkeys method and static sendwait) to the password form. The RunMacro method brings the form and automation pauses waiting for the password to be entered. It doesnot moves to the next block so that I can sendkeys. Basically, the automation doesn't continue until the password form goes away. I tested clicking "OK" and "Cancel" using test control method and it works but sendkeys doesn't.
Pegasystems Inc.
US
You will probably need to use a MonitorAll adapter for Excel. You might then be able to interrogate that form and enter text that way.
1. Create a new Windows Application.
2. Set the StartMwthod to MonitorAll
3. Set the Path to Excel.exe (you can turn off ResolvePath to allow you to enter just Excel.exe).
4. Click Start Interrogation.
5. Open Excel manually and navigate to the password form in question and try to interrogate it.
Coforge BPM
US
Thomas,
I have already tried this. Part of the problem is automation does not move to the next step (from RunMacro method to SendKeys) so that it can execute the SendKeys Method. It hits the RunMacro method brings the enter password form and stays there. When I cancel the form then the automation hits the SendKeys Method which doesn't help because I need to use send keys when the form is present. On interrogation side, I cannot interrogate the "text box" where I can send keys in the form (as shown in my original post attachment) but right now problem I am having is before I can execute the Sendkeys. I tried WaitForEvent on the form (as shown in the attachment I uploaded with my original question) so that it recognize the form is there but again same problem.
Regards,
Dipesh
Pegasystems Inc.
US
I understand now. Call RunMacro on one thread of a ParallelProcess and then call the wait for create/Send keys on another. Use the bottom link to continue after you have closed the dialog.
Coforge BPM
US
Thomas,
I tried that. This is what I have done:
It doesn't hit the breakpoint after executing RunMacro method (From Fired node). Looks like Control2 is not matching at runtime. It always shows matched when I run interrogation and use test controls.
If you have got any further ideas please let me know.
Dipesh
Pegasystems Inc.
US
Because RunMacro is synchronous, the wait for event will always timeout. You should call RunMacro on one thread of a parallel process and then wait for create and enter the password and close the dialog on another.
Coforge BPM
US
I did exactly what you have suggested (attached). It is still failing to match the control during runtime. In fact, none of the controls are matching in runtime. However, they all matched while running the adapter. I tried adding Rematch method and longer WaitForCreate (2mins) but no luck. There is no flexibility as far as match rules because I cannot use text editor and properties of the control are limited. Do I need to set any properties for adapter?
I have following properties set for the adapter:
ChildHookProcess-True
Path-Excel.exe
StartMethod-MonitorAll
StartOnProjectStart-False
MatchRules used-Windows style match rule (Type-child)
Accepted Solution
Pegasystems Inc.
US
Following solution design was implemented to help Dipesh with password protected workbook and worksheet.
The solution opens a password protected and unprotects the active excel sheet of that workbook.
Please note the new Open Microsoft Excel connector method used in the solution is only available in Robotics Version 8.0.2010 and later.