problem when getting collection

[absak]

Hello,

I had to read a "sheet" as a collection, but the values read are wrong: in the Excel File : 1234567890123 but the values found in the collection is 1,23E+12, is there any solution to this?

 

[Anjaneyareddy]

No.you make sure that in Excel where the Numbers appear u must shoud data type as a Number.
By Default (General) is there in Excel ,so u can change that General - Number, then BP gets the Exactly Data.

then our BP Get those data.

Thanks
Reddy.

 

[absak]

Thank you,


can we change it in BP?

 

[Anjaneyareddy]

No need of change in BP
it Gets whatever there in Excel

 

[absak]

Hello, i can't change it in the csv file because it is outputed by another webpage

 

[RavitejaKiran]

Hi Absak, Yes we can do it with Blueprism.
There are 2 ways to do it.
1) One is to Create a loop of Format cell. Take a counter data item of initial value 1.
a. Create instance
b. Open Workbook
c. Get number of rows
d. Format Cell - Cell reference is "A"&[Counter] ; Format is "##,###" Note: A can be changed according to the column required.
e. Decision(Counter = Number of Rows) if No , Calc stage to Increment counter and loop back to point d.
f. If Yes, Get data into collection and then Close Workbook and instance.
2) The second one is simple but need to change the code in excel VBO.
Create a new action in Excel VBO and copy the code of FormatCell and modify the code stage of Format Cell like below
GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref,cellref1).NumberFormat = format
This time the inputs of Format cell should have cellref(StartCell) and cellref1(EndCell).

Hope this will resolve your query. Please let me know if that worked.

 

[absak]

Can you please shematize the First solution, creating a loop on what?

 

[RavitejaKiran]

Take a counter of value 1
1. Create instance & Open Workbook
2. Get number of rows
3. Format Cell - Inputs : Cell reference is "A"&[Counter] ; Format is "##,###" Note: A can be changed according to the column required.
4. DecisionStage (Counter = Number of Rows) if No , Calc stage to Increment counter and loop back to step 4.
5. If Yes, Get data into collection and then Close Workbook and instance. Attached the screenshot please refer.

 

[absak]

I'm gettting an error: Internal : Could not execute code stage because exception thrown by code stage: The given key was not present in the dictionary. at "Format Cell"

counter is a number
View attachment 1550505568370.png


View attachment 1550505300529.png