How copy collection to clipboard

mbailey

Member
Hi,

My process interacts with SAP and Excel. One of the tasks is to copy a column of values (no more than 100 items) from an Excel worksheet and paste it into separate rows of a table on an SAP screen. I use the MS Excel VBO Select and Copy actions to place the values in the clipboard and the SAP screen has an Import Clipboard button that pastes the copied items into contiguous rows of the table. The process is in a loop that handles many items and each iteration requires the values extracted from Excel to be pasted into SAP. That means I need to keep the worksheet open and read the desired values in each iteration. The values in the Excel file can change on a daily basis but, once read, can be considered constant for the duration of a single run, regardless of the number of iterations. I want to read these values once at the beginning of a run, put them into a collection, then close the Excel file and click the SAP button to paste those values into the table for each iteration of the loop. I can use the MS Excel VBO Get Worksheet Range As Collection action to put the data into a collection but I don't know how to put that collection of values into the clipboard for each iteration (the clipboard is used in other activities so the clipboard contents do not persist beyond a single iteration). The Utility-Environment Set Clipboard takes a text argument rather than a collection and the Utility-Collection Manipulation Copy Rows action cannot output to the clipboard.

Does anyone know how I can populate the clipboard from a collection so that, when pasted, the items appear on separate rows rather than a long string on a single row of the table?

Thanks,
Michael Bailey
 

Sachin_Kharmale

Active Member
Hi Michael Bailey ,

If you want to copy Collection to Clipboard i have design code stage it is working fine for me.
1)You Just need to give input Collection
2)Collection Start Row to be Copied
3)Number of rows to be Copied
it will copies all required data in system clipboard
View attachment 1559629262070.png
Fig 1- Blue Prism Action added As Copy collection to Clipboard
View attachment 1559629344226.png
Fig 2- Input To Code Stage
View attachment 1559629400640.png
Fig 3 - Code Stage Part 1
View attachment 1559629608949.png
Fig 4-Code Stage Part 2

Hope it will work for you .

Best,
Sachin
 

mbailey

Member
Sachin,

That worked great, was easy to implement, and uses a small modification to existing object code. Thanks for the help.

Regards,
Michael Bailey
 

incorporated8

New Member
Hi Michael Bailey ,

If you want to copy Collection to Clipboard i have design code stage it is working fine for me.
1)You Just need to give input Collection
2)Collection Start Row to be Copied
3)Number of rows to be Copied
it will copies all required data in system clipboard
View attachment 3947
Fig 1- Blue Prism Action added As Copy collection to Clipboard
View attachment 3948
Fig 2- Input To Code Stage
View attachment 3949
Fig 3 - Code Stage Part 1
View attachment 3950
Fig 4-Code Stage Part 2

Hope it will work for you .

Best,
Sachin
How would you include the field names when you copy to clipboard?
 

nbargaje

Member
Hi Michael Bailey ,

If you want to copy Collection to Clipboard i have design code stage it is working fine for me.
1)You Just need to give input Collection
2)Collection Start Row to be Copied
3)Number of rows to be Copied
it will copies all required data in system clipboard
View attachment 3947
Fig 1- Blue Prism Action added As Copy collection to Clipboard
View attachment 3948
Fig 2- Input To Code Stage
View attachment 3949
Fig 3 - Code Stage Part 1
View attachment 3950
Fig 4-Code Stage Part 2

Hope it will work for you .

Best,
Sachin



Hi Sachin,

i am getting below error for this code, can you please help me on this. code is copy paste.
Thanks in advance.
 

Attachments

  • flow.png
    16.6 KB · Views: 263
  • error.png
    26.3 KB · Views: 209

Sachin_Kharmale

Active Member
Hi Sachin,

i am getting below error for this code, can you please help me on this. code is copy paste.
Thanks in advance.
Hi ,

line 18 Error - Define Output_Collection to store output
line 5 Error - Add system.text.stringbuilder namespace in Initialization stage of object
line 33 Error - Define Output_Collection to store output
line 21 Error- Define Output_Collection to store output
line 38 Error- Define Success boolean data item to store output
line 41 Error- Define Error_Message text data item to store output

I hope it will help you !.
 

nbargaje

Member
Hi ,

line 18 Error - Define Output_Collection to store output
line 5 Error - Add system.text.stringbuilder namespace in Initialization stage of object
line 33 Error - Define Output_Collection to store output
line 21 Error- Define Output_Collection to store output
line 38 Error- Define Success boolean data item to store output
line 41 Error- Define Error_Message text data item to store output


I hope it will help you !.


Sachin thanks for your reply. i did what you said. but can you help for error line 37 and 29.
or you can see new attached file which have only 2 errors. i am really not getting where i can declare 'chr' 'clipboard ' variable.
 

Attachments

  • 2 error.PNG
    6.9 KB · Views: 137

Sachin_Kharmale

Active Member
Sachin thanks for your reply. i did what you said. but can you help for error line 37 and 29.
or you can see new attached file which have only 2 errors. i am really not getting where i can declare 'chr' 'clipboard ' variable.
Hi,

Make sure you have added windows.forms.dll reference in init stage
as mentioned in bellow screen shot
View attachment 1567759016247.png


Also refer the bellow code it will copy collection to windows clipboard.

C#:
Try[/SIZE][/FONT][/COLOR]
[SIZE=3][COLOR=rgb(44, 130, 201)][FONT=verdana]    Dim strBuilder As New StringBuilder
    For J As Integer = 0 To Source_Collection.Columns.Count - 1
        strBuilder.Append(Source_Collection.Columns(J).ColumnName & chr(9))
    Next
    strBuilder.AppendLine()
    'copy the requested rows one by one
    For I As integer = 0 To Source_Collection.Rows.Count -1
        Dim Values(Source_Collection.Columns.Count - 1) As Object
        For J As Integer = 0 To Source_Collection.Columns.Count - 1
            Values(J) = Source_Collection.Rows(I)(J)
            strBuilder.Append(Values(J) & chr(9))
        Next
        strBuilder.AppendLine()
    Next       
    Clipboard.SetDataObject(strBuilder.ToString,True)
    Success = True
Catch Ex As Exception
    Success = False
    Error_Message = Ex.ToString()[/FONT][/COLOR][/SIZE]
[COLOR=rgb(44, 130, 201)][FONT=verdana][SIZE=3]End Try

Code stage Input and Outputs -
View attachment Code Stage Input and output.png

I hope it will help you.
 

nbargaje

Member
Sachin,

That worked great, was easy to implement, and uses a small modification to existing object code. Thanks for the help.

Regards,
Michael Bailey
Hi,

Make sure you have added windows.forms.dll reference in init stage
as mentioned in bellow screen shot
View attachment 4546


Also refer the bellow code it will copy collection to windows clipboard.

C#:
Try[/SIZE][/FONT][/COLOR]
[SIZE=3][COLOR=rgb(44, 130, 201)][FONT=verdana]    Dim strBuilder As New StringBuilder
    For J As Integer = 0 To Source_Collection.Columns.Count - 1
        strBuilder.Append(Source_Collection.Columns(J).ColumnName & chr(9))
    Next
    strBuilder.AppendLine()
    'copy the requested rows one by one
    For I As integer = 0 To Source_Collection.Rows.Count -1
        Dim Values(Source_Collection.Columns.Count - 1) As Object
        For J As Integer = 0 To Source_Collection.Columns.Count - 1
            Values(J) = Source_Collection.Rows(I)(J)
            strBuilder.Append(Values(J) & chr(9))
        Next
        strBuilder.AppendLine()
    Next       
    Clipboard.SetDataObject(strBuilder.ToString,True)
    Success = True
Catch Ex As Exception
    Success = False
    Error_Message = Ex.ToString()[/FONT][/COLOR][/SIZE]
[COLOR=rgb(44, 130, 201)][FONT=verdana][SIZE=3]End Try

Code stage Input and Outputs -
View attachment 4547

I hope it will help you.


Thank you so much Sachin for quick response. it worked.

Thanks,
Nitin
 

Piyu

New Member
Hi ,

line 18 Error - Define Output_Collection to store output
line 5 Error - Add system.text.stringbuilder namespace in Initialization stage of object
line 33 Error - Define Output_Collection to store output
line 21 Error- Define Output_Collection to store output
line 38 Error- Define Success boolean data item to store output
line 41 Error- Define Error_Message text data item to store output


I hope it will help you !.
Thanks Sachin it works for me.
 

Piyu

New Member
Hi,

Make sure you have added windows.forms.dll reference in init stage
as mentioned in bellow screen shot
View attachment 4546


Also refer the bellow code it will copy collection to windows clipboard.

C#:
Try[/SIZE][/FONT][/COLOR]
[SIZE=3][COLOR=rgb(44, 130, 201)][FONT=verdana]    Dim strBuilder As New StringBuilder
    For J As Integer = 0 To Source_Collection.Columns.Count - 1
        strBuilder.Append(Source_Collection.Columns(J).ColumnName & chr(9))
    Next
    strBuilder.AppendLine()
    'copy the requested rows one by one
    For I As integer = 0 To Source_Collection.Rows.Count -1
        Dim Values(Source_Collection.Columns.Count - 1) As Object
        For J As Integer = 0 To Source_Collection.Columns.Count - 1
            Values(J) = Source_Collection.Rows(I)(J)
            strBuilder.Append(Values(J) & chr(9))
        Next
        strBuilder.AppendLine()
    Next       
    Clipboard.SetDataObject(strBuilder.ToString,True)
    Success = True
Catch Ex As Exception
    Success = False
    Error_Message = Ex.ToString()[/FONT][/COLOR][/SIZE]
[COLOR=rgb(44, 130, 201)][FONT=verdana][SIZE=3]End Try

Code stage Input and Outputs -
View attachment 4547

I hope it will help you.

Thanks its works like charm.
 

amantiwari

New Member
Hello I am getting following errors:

Clipboard not declared

Chr is not declared

Clipboard is not defined

Please help me with these errors

as i don't know from where to find the dlls and where to import them
 
Top