Blueprism Copy and paste filter data in excel

rohit.x07

New Member
I have requirement to filter the records on the basis of one criteria and after comparing both the sheets, paste the records into another sheet.
In both the excels the templates are same and there first three rows and last row are merge rows.

What i did: After perform all my bot functionality i have created the extended vbo to filter the records and than copy an paste the filter records.
My filter functionality is working fine, but when i am doing copy & paste it is giving me error always its in screenshot.

May be that is happening to copy and paste the filter records as i gave the complete source ranges in the input as attached in the screenshot.

Please have a look and share your solutions if you guys faced this problem or anybody knows the solution.
your views are valuable thanks in advance.

If any confusion or more clarification please let me know.
 

Attachments

  • Copy and paste.png
    79.2 KB · Views: 241
  • FLow Diagram.png
    142.3 KB · Views: 174
  • Error On Copy and paste.png
    55.8 KB · Views: 139
  • Filter Records.png
    20.7 KB · Views: 135

sivagelli

Well-Known Member
When you are using 'Copy and Paste Range', you have to open the source and destination workbooks before using 'Open Workbook' action. And, you have to use just the workbook names that come as output from the 'Open Workbook' action (not the path of the files). If I follow your Flow Diagram that you attached, I do not see the destination file is opened; so is the exception.

Also, refer to the thread that details about the same exception you had for action 'Copy and Paste Range'.
 

rohit.x07

New Member
When you are using 'Copy and Paste Range', you have to open the source and destination workbooks before using 'Open Workbook' action. And, you have to use just the workbook names that come as output from the 'Open Workbook' action (not the path of the files). If I follow your Flow Diagram that you attached, I do not see the destination file is opened; so is the exception.

Also, refer to the thread that details about the same exception you had for action 'Copy and Paste Range'.

Hi sivagelli, i have already open the workbook of destination workbook by using the show workbook in the sub page of project calculation, And before copy paste i am able to see all the actions, but i used show workbook after the open workbook action.

I saw one below thread in which the solution is to enter the workbook names not the complete path. so for that source file name i used the open workbook name action from there in the output i am getting the workbook names and that workbook name i am using in the copy and paste action.

Also if i give directly the workbook names in the copy & paste action still i am getting the same error.

In the below snapshot i have attached the flow diagram of both the pages with input and output data values.

Please share your views where i am doing something wrong.

https://www.rpaforum.net/threads/question-about-ms-excel-vbo-copy-and-paste-worksheet-range.580/
 

Attachments

  • Flow Diagram Main Page.PNG
    43.7 KB · Views: 89
  • Flow Diagram Subpage Project Calculation.PNG
    57.5 KB · Views: 67

sivagelli

Well-Known Member
i have already open the workbook of destination workbook by using the show workbook in the sub page of project calculation, And before copy paste i am able to see all the actions, but i used show workbook after the open workbook action.
When you say you are opening the file in a sub-page, are you using the handle in the main page or do you have a different handle in the sub-page. Also, the workbook name stored in a data item which comes as an output from 'Open Workbook' is exposed to Main page?

Try using same 'handle' and expose the workbook name from subpage to main page.

Post back, how it goes.
 

rohit.x07

New Member
When you say you are opening the file in a sub-page, are you using the handle in the main page or do you have a different handle in the sub-page. Also, the workbook name stored in a data item which comes as an output from 'Open Workbook' is exposed to Main page?

Try using same 'handle' and expose the workbook name from subpage to main page.

Post back, how it goes.

Currently i am using the different handles for both the workbooks for DeptWorkbook i am using handle name as handle and for another workbook name Project workbook i am using different handle as Project handle. And the workbook name stored in a data item which comes as an outpur from 'Open workbook' i used it as global data item.
For reference i am attaching the screenshot for create instance and open workbook
 

Attachments

  • Page 1 handle.png
    68.8 KB · Views: 64
  • Page 1 Open workbook.png
    110.3 KB · Views: 57
  • Page 2 handle.png
    91.8 KB · Views: 45
  • Page 2 Open workbook.png
    107.8 KB · Views: 46
  • Copy and paste.png
    86.4 KB · Views: 75

sivagelli

Well-Known Member
Looking at the expression that is building vlookup() formula, I guess the error is to do with your vlookup(). The expression once evaluated is giving you the below result which is having the worksheet name twice.
You are passing worksheet name 'Page1' thru a variable [Dept_Sheet_Name] and also have 'Page1' hard coded in the expression. Remove either one of them, unless it is needed.

=vlookup(A5,'c:\Rohit\Learning\Blue Prism\Exercise\Anita\Department\[Dept.xlsx]Page1Page1'!$A$5:$B$70,2,FALSE)

Post back, how it goes.
 

rohit.x07

New Member
Looking at the expression that is building vlookup() formula, I guess the error is to do with your vlookup(). The expression once evaluated is giving you the below result which is having the worksheet name twice.
You are passing worksheet name 'Page1' thru a variable [Dept_Sheet_Name] and also have 'Page1' hard coded in the expression. Remove either one of them, unless it is needed.

=vlookup(A5,'c:\Rohit\Learning\Blue Prism\Exercise\Anita\Department\[Dept.xlsx]Page1Page1'!$A$5:$B$70,2,FALSE)

Post back, how it goes.

the vlookup is correct after one filter i am closing the excel and than again opening with the same handle than it works, its strange how it happens if you have any guide or study material related to read in depth of handle functionality please share it we great to get more in depth.

But i am facing one small problem after doing VLookup, filteration of records and doing copy & paste only the values are copying not the complete VLookup formulae, will it be possible to copy and paste the complete with VLookup.
 

sivagelli

Well-Known Member
the vlookup is correct after one filter i am closing the excel and than again opening with the same handle than it works, its strange how it happens if you have any guide or study material related to read in depth of handle functionality please share it we great to get more in depth.

But i am facing one small problem after doing VLookup, filteration of records and doing copy & paste only the values are copying not the complete VLookup formulae, will it be possible to copy and paste the complete with VLookup.
Are you now able to copy paste range successfully?
 

rohit.x07

New Member
the vlookup is correct after one filter i am closing the excel and than again opening with the same handle than it works, its strange how it happens if you have any guide or study material related to read in depth of handle functionality please share it we great to get more in depth.

But i am facing one small problem after doing VLookup, filteration of records and doing copy & paste only the values are copying not the complete VLookup formulae, will it be possible to copy and paste the complete with VLookup.

i did the Paste special it works thanks.
 

Justin Nguyen

New Member
How can I filter and sum Collection column in the same Workbook ... ?
 

Attachments

  • filter sum 1.PNG
    13.1 KB · Views: 59
  • filter sum 2.PNG
    12.1 KB · Views: 48
  • filter sum.PNG
    32.4 KB · Views: 48
Top