How to pass dynamic sheet names in blue prism

[VSETHI]

Hi All,

Help in dynamically passing Worksheet Name in blue prism.

I am storing the excel sheet "Environment" as an collection which contains all the Sheet Name in one column called "Table_NAME".

Eg:
Sheet 1 = Environment sheet [Which contains the name of all other sheets [i.e sheet2,sheet3] under "Table_Name" column]
Sheet 2 = MARA
Sheet 3 = MATNR

Now i want to dynamically pass the "Sheet names" based on the TABLE_NAME" to copy some data from their respective sheets.

If i pass the sheet name as collection eg: "Worksheet = [Input.Table_Name]" ,it gives me an error as "The required data type is Text but the data type of [Input] is Collection."

How can i avoid hard coding of sheet name and instead pass it from collection or any other method dynamically?

Any help is much appreciated!!

View attachment 1529779672180.png

 

[VSETHI]

If i am trying to pass the worksheet name as &[Input.Table_Name] ,getting an error as Internal : The & operation must have two operands

View attachment 1529779609478.png

 

[VJR]

Hi VSETHI,

Remove the '&' sign and it will work as the format is - [CollectionName.ColumnName].
To go through every row of the collection (which contains the sheet name), apply a loop on the collection.

 

[VSETHI]

Hi VSETHI,

Remove the '&' sign and it will work as the format is - [CollectionName.ColumnName].
To go through every row of the collection (which contains the sheet name), apply a loop on the collection.

Hi VJR,

I tried passing [CollectionName.ColumnName] but it gives me an error as "The required data type is Text but the data type of [Input] is Collection."

The intent here is to pass collection value for column table_name to a "Sheet" field which doesnt accept any other data type other than "Text".

 

[VJR]

Hi VSETHI,

[CollectionName.ColumnName] should be passed to the sheet parameter and it should indeed be a text. Looks like you may have the collection column data type incorrectly configured. Are you able to show how your collection Input and the Table_Name column looks like?

 

[VSETHI]

Hi VSETHI,

[CollectionName.ColumnName] should be passed to the sheet parameter and it should indeed be a text. Looks like you may have the collection column data type incorrectly configured. Are you able to show how your collection Input and the Table_Name column looks like?

Sure ,will send you a screenshot.

 

[VSETHI]

Sure ,will send you a screenshot.

As seen in below screenshot if i drag my "Input" collection to Sheet field,it shows the error:

View attachment 1529834023837.png

 

[VJR]

Hi VSETHI,

The error is occurring since you have dragged only the collection [Input].
Looks like you have a Dynamic collection in your diagram where the columns are generated dynamically and hence you do not see the column names on the right hand side.
As suggested above, simply put this format [CollectionName.ColumnName] in the sheet parameter (assuming you have a loop) and you will find it working.

 

[VSETHI]

Is there a way i can store the collection excel data in a text format and then pass it?

The issue here is anything i pass in the sheet field is considered as a string:

Eg:
1. ["Input.Table_Name]" is also considered as string whereas i want to pick a dynamic sheet name based on "Table_Name"

I was trying to use & earlier to fetch the value of collection but it seems everything was considered as string.

 

[VJR]

The format I provided you does not contain any double quotes
[CollectionName.ColumnName]

 

[VJR]

As seen in below screenshot if i drag my "Input" collection to Sheet field,it shows the error:

View attachment 1182

It appears from your screenshot that the font is overlapping the lines and you may want to attempt to fix the issue by applying the below solution in Post #7.
http://www.rpaforum.net/threads/display-visualization-problems.445/post-601

 

[VSETHI]

Have updated the sheet name as [CollectionName.ColumnName] which is [Input.Table_Name] and you are right its a loop.

Will try once and let you know..Also if you skype business email i can do a screen share with you on the issue which would be faster to resolve.
View attachment 1529834817456.png

 

[VSETHI]

It appears from your screenshot that the font is overlapping the lines and you may want to attempt to fix the issue by applying the below solution in Post #7.
http://www.rpaforum.net/threads/display-visualization-problems.445/post-601

Thanks for the post

Helped to resolve my font issue.

View attachment 1529835133741.png

 

[VJR]

Looks great now.

 

[VSETHI]

Getting this error now even though i have configured start loop from collection:



View attachment 1529835560021.png

 

[VJR]

When are you getting the error and at which stage? Reset, Refresh and run the process again.

 

[VSETHI]

When are you getting the error and at which stage? Reset, Refresh and run the process again.

Tried reset,refresh and run still same issue as previous screenshot.

Error is happening at "Loop Start 3" stage wherein i have configured an "Input" collection still getting error.

 

[VJR]

Where is the collection named Input in the above screenshot. Is it in some other object page?

 

[VSETHI]

Where is the collection named Input in the above screenshot. Is it in some other object page?

Collection name "Input" is in the process studio.

View attachment 1529836706860.pngView attachment 1529836801819.png

 

[VJR]

Can you show where and how are you passing the Input collection from the Process to the Object.