Excel data sorting through BluePrism

[cs.andras]

Hi,

Has anyone done such a thing? I've tried to modify my copy of MS Excel VBO, adding a new page (action) based on the Select method, but got HRESULT: 0x800A03EC on the Range command when ran the code stage.

Thanks,
Andras

The code I wrote is:

Dim wb, ws As Object
Dim excel, sheet, Range As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
Range = sheet.Range(SortableRange)
Range.Select()

ws.Sort.SortFields.Clear

Dim i as Integer

For i = 1 To SortingColumns.Rows.Count
    ws.Sort.SortFields.Add (Key:=Range(SortingColumns.rows.item(i).item(1)), SortOn:=Excel.xlSortOnValues, Order:=Excel.xlAscending, DataOption:=Excel.xlSortNormal)
Next i

ws.Sort.SetRange (Range(SortableRange))
ws.Sort.Header = Excel.xlGuess
ws.Sort.MatchCase = False
ws.Sort.Orientation = Excel.xlTopToBottom
ws.Sort.SortMethod = Excel.xlPinYin
ws.Sort.Apply


Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
End Try

 

[VJR]

Hi Andras,

I just stumbled across this post today.
Are you still looking for a resolution on this or is it all sorted out now.

 

[radhavydehig]

Hi ... Do we need to write the code for sorting collection-Ascending order.... (Utility -Collection Manipulation)..
i m trying it... but no able to to do so...... can anyone please suggest me how to do it ....

 

[VJR]

Hi radhavydehig,

It is not clear of your exact requirements as the title of the thread is sorting excel but your comments are about sorting a collection.
Can you elaborate the full details of what you are trying to do, what have you done so far and what is it you said that you are not able to do so.

 

[radhavydehig]

Sorry If I post in wrong thread... I was trying to sort numbers... in Collection by using "Sort Collection".. in Utility -"Collection manipulation" ...but any ways .. its resolved ... thanks for replying

 

[Ujjaldas]

Hi VJR/Rasdhavydehig,
I am getting similar type of issue when I am trying to get the converted amount from x-rates.com website for some base and quoted currency.
Can you please help me to resolve the issue
Please see the attached screenshot

 

[Vivek Gupta]

Hi Andras,

Did you get the solution for sorting the data in excel? If yes, Kindly get.

My requirement is to sort the data in excel using Blue Prism.

Thanks in Advance!
Regards,
Vivek Gupta

 

[simran kumari]

hi,

i need to take excel sheet data in Blue prism collection and filter column data like "Location" wise and store result in another execl sheet. please help me on it

 

[mhenderson]

Hi Simran, did you get a response or resolve this issue. I am facing the same problem using "Utility - Collection manipulation" to sort data in column D of an excel spreadsheet. However the available inputs only relate the the collection, ascending order and "Sort Field" leading me to think it is unable to sort columns as it does not recognise column D.

 

[i_am_clinton]

I understand how to sort in collection but does anyone know how to sort directly in an excel sheet? Reason for this is because if I sort in collection and write back to excel, I lose a lot of formatting and formulas etc. Any excel vbo code is much appreciated!

 

[saipallavi_gampala]

Hi Clinton,

May I know how you have sorted directly in excel,I have a requirement to sort the data asper dates,

Thanks in advance.

 

[Faith D]

Hi Clinton,

May I know how you have sorted directly in excel,I have a requirement to sort the data asper dates,

Thanks in advance.

Hi,
Were you able to figure this out?