Excel instance issue inside a loop

[ParnabSinha_Cho]

Hi All,

Good Morning...

I am currently facing an issue while performing excel operations inside a loop. I have to apply multiple excel functions(vlookup, IF) in a workbook. I have placed the function values, column name in a collection and the using the collection to loop through the excel and perform each functions. I am creating the excel instance and opening the workbook outside the loop and inside the loop I am just performing the copy paste functions and finally outside the loop I am copying the workbook values in a collection. The issue is inside the loop I keep facing either of these issues

1. "The given key was not present in the dictionary." Facing this even with Attach Instance.
2. "The workbook xyz.xls is not present in the instance".

There is one fix available, ie., to create and close instance inside the loop but that is time consuming and some how I feel is a waste of resource.

In the attached screenshot you can see the flow. Any suggestion on how we can fix this issue is very much appreciated.

Thanks,
Parnab

------------------------------
Parnab Sinha Choudhury
RPA Developer / Business Analyst
------------------------------

[michaeloneil]

Hi Parnab

Have you tried using the "Attach to Workbook" action in the excel vbo? This might work a little better than the normal attach action.

Let us know if this worked. :)​

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[ewilson]

@ParnabSinha_Cho,

To be clear, are you working with multiple instances of Excel and different workbooks or a single instance? Just trying to understand why you're calling Attach Instance in the loop.

Cheers,
​

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ParnabSinha_Cho]

Hi @ewilson,

Thanks for responding. I am using a single instance and a single workbook and updating the excel in a loop. If I am not using the Attach Instance inside the loop then I keep getting "The given key was not present in the dictionary." error. Using attach instance I am able to avoid this error.

Any suggestion on how to proceed from here?

Thanks,
Parnab​​

------------------------------
Parnab Sinha Choudhury
RPA Developer / Business Analyst
------------------------------

[ParnabSinha_Cho]

Hi @Michael ONeil

Thanks for responding and sharing ​this tip. Actually I used Attach To Workbook, and even then also I was getting this issue as I was traversing in the loop multiple times. Also as Attach To Workbook expects me to provide a filename and is not  easy to work with scenarios where we are using create workbook.

Thanks,
Parnab

------------------------------
Parnab Sinha Choudhury
RPA Developer / Business Analyst
------------------------------

[ewilson]

Ah, ok. You're original message mentioned you were facing the issue even if you included the Attach statement.

What action is being called in the stage shown in the screenshot called "Go to Second Ro"? At what stage are you getting the exception? Does it happen at the same stage all the time?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[michaeloneil]

Hi Parnab

Was there any reason you couldnt use write collection? Are you writing each row of the collection to various different cells e.g. A1, C6 etc? Are you writing a formula that links to a different file? Sometimes an issue can occur with this as excel might ask try throw a pop up when a link is directed to a different file or ask you to update links. Can you also share an example of the formulas you are writing to so we can determine if there is an issue in there.

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[ParnabSinha_Cho]

Hi @ewilson,

You are right i do get errors even with Attach Instance but that is may be in 1 out of 15-20 runs, but without Attach Instance I do get error for sure.

I usually get the error in Go To Second Row stage, here I am using Go To Cell action. 

Can you kindly let me know if any instance, workbook created outside a loop is supposed to be active inside a loop too? or is the scope different there?

Thanks,
Parnab

​

------------------------------
Parnab Sinha Choudhury
RPA Developer / Business Analyst
------------------------------

[ParnabSinha_Cho]

Hi @ewilson @Michael ONeil

Thanks for both of your suggestion. When nothing was working I imported a new MS Excel VBO and used that. Surprisingly it was working fine. Not sure what was the issue with other VBO as it was working fine in other processes. In case if anyone else is facing similar issue, better use another Excel ​VBO instead of spending too much time on analysing why.

Thanks,
Parnab

------------------------------
Parnab Sinha Choudhury
RPA Developer / Business Analyst
------------------------------