Community Chat

 View Only
last person joined: yesterday 

A space for discussion around the Community site and programs.

  • 1.  COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-15-2022 11:35


    ------------------------------
    Oyinkansola Musa
    ------------------------------


  • 2.  RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-20-2022 14:34
    You can achieve this using BP code stage

    ------------------------------
    Nilesh Jadhav
    Senior RPA Specialist
    ADP
    India
    ------------------------------



  • 3.  RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-20-2022 15:24
    @Nilesh Jadhav how do i go about this?


    ------------------------------
    Oyinkansola Musa
    ------------------------------



  • 4.  RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-20-2022 23:39
    Hi @Oyinkansola Musa,

    This issue even I had faced in the past and for this you have to actually create a custom action using a tiny little modification in one of the existing code stages. First, I would recommend you to download​ the latest 'MS Excel - Extended' VBO business object which is also available in the following DX Exchange URL: Function for MS Excel VBO - Extended - 2.3.3

    Solution Explanation:

    You need to create a duplicate action, 'Copy and Paste Worksheet Range' for one of the existing actions within this business object. You can name your new action as 'Copy and Paste Worksheet Range - Visible Cells' as shown below:





    You should now see a duplicated action with this new name getting created:



    Here you need to change the name of the code stage to 'Paste Worksheet - Visible Cells' as I have done above. Now inside the code stage you need to paste the exact same code as I have shown below:

    Dim sw, dw As Object
    Dim ss, ds As Object
    Dim excel, sheet, source, destination, cells, cell As Object
    Const xlCellTypeVisible As Int32 = 12
    
    Try
    
    sw = GetWorkbook(Handle, Source_Workbook)
    dw = GetWorkbook(Handle, Destination_Workbook)
    
    ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
    ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)
    
    sw.Activate()
    ss.Activate()
    excel = ss.Application
    sheet = excel.ActiveSheet
    cell = excel.ActiveCell
    
    If Source_Range="" Then
    	cells = sheet.Cells.SpecialCells(xlCellTypeVisible)
    	Destination_Range = "A1"
    Else
    	cells = sheet.Range(Source_Range).SpecialCells(xlCellTypeVisible)
    End If
    
    cells.Select()
    source = excel.Selection
    source.Copy()
    cell.Select()
    
    dw.Activate()
    ds.Activate()
    sheet = excel.ActiveSheet
    cell = excel.ActiveCell
    destination = sheet.Range(Destination_Range)
    destination.Select()
    sheet.Paste()
    cell.Select()
    
    My.Computer.Clipboard.Clear()
    
    Success = True
    
    Catch e As Exception
    	Success = False
    	Message = e.Message
    Finally
    	sw = Nothing
    	ss = Nothing
    	dw = Nothing
    	ds = Nothing
    	excel = Nothing
    	sheet = Nothing
    	source = Nothing
    	destination = Nothing
    	cells = Nothing
    	cell = Nothing
    End Try​


    Here, just to explain you I have added one line at the top to the original code: Const xlCellTypeVisible As Int32 = 12 and then I am using it at places like: cells = sheet.Cells.SpecialCells(xlCellTypeVisible) and cells = sheet.Range(Source_Range).SpecialCells(xlCellTypeVisible)

    These lines help me to tell the code that it only needs to pick up the visible cells on the screen apart from the entire range which the original code did by default.

    Testing For Solution:

    Now, publish your action and then use all the actions of the same business object where you have done the changes in your process studio while interacting with the excel file in order to avoid any exceptions. I have a sample process studio workflow shown below:



    So just to explain you a sample scenario which I have picked up, I basically have an excel file with below original table data with an applied filter on top of it and I need to paste this data completely to a blank sheet called as 'Results' which already exists:

     



    Now, in order for this solution to work remember it should not be used in background mode otherwise it can throw errors and that is reason why you must use a Show action in between as I have used in my workflow. Hence, I am creating an excel instance, then opening my excel file, then making it visible on the screen and then I use my new created action which has the below parameters:



    Here, I have left the source range parameter as blank since I want to copy all the cells and not define any range to it. Post execution of the workflow I get the below results:



    So here as you can see you can provide the proper source and destination details to get the desired filtered copy paste operation completed.


    ------------------------------
    ----------------------------------
    Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

    Regards,
    Devneet Mohanty
    Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
    WonderBotz India Pvt. Ltd.
    Blue Prism Community MVP | Blue Prism 7x Certified Professional
    Website: https://devneet.github.io/
    Email: devneetmohanty07@gmail.com

    ----------------------------------
    ------------------------------



  • 5.  RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-21-2022 10:30
    @Devneet Mohanty
    This solution works. However if I wan to copy the data from the result to employees ​back without removing the filter on the result worksheet how will i go about that?

    ------------------------------
    Oyinkansola Musa
    ------------------------------



  • 6.  RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-21-2022 10:34
    Hi,

    Just a confusion I have, if you have copied the data from Employees to Result then why would you want to copy the same data again from Result to Employees. Employees is a filtered sheet here whereas Results is an unfiltered sheet consisting of a subset of data from your employees. So kind of doesnt make sense why would you want that since Employees is anyway going to have all that data which is in Results?

    ------------------------------
    ----------------------------------
    Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

    Regards,
    Devneet Mohanty
    Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
    WonderBotz India Pvt. Ltd.
    Blue Prism Community MVP | Blue Prism 7x Certified Professional
    Website: https://devneet.github.io/
    Email: devneetmohanty07@gmail.com

    ----------------------------------
    ------------------------------



  • 7.  RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-21-2022 10:43
    @Devneet Mohanty

    I have a list of records called Employee, I went ahead to filter the worksheet and copied the visible cells to the result worksheet. However, I need to perform some manipulation on the records in the result worksheet maybe for example replacing some values with another then I need to copy the result worksheet back to the employee worksheet since the result worksheet has been worked on.​

    ------------------------------
    Oyinkansola Musa
    ------------------------------



  • 8.  RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

    Posted 06-21-2022 10:59
    @Oyinkansola Musa,

    I get your requirement now but don't you think such an approach will make your automation bit complicated as what you are asking is just not copying the values back but more like overwriting the values I think. The problem here with this approach is that if your ranges are scattered like let say one record is at position 1 and another is at position 5. To exactly replace those values you will end up with a lot of issues.

    One approach which I can suggest in such a case would be to first filter the Employees sheet and then you perform manipulation over that same filtered dataset in the original sheet without even copying it to Results sheet and then turn off the filter once the job is done. Would this approach work for you?​

    If not, then as per your scenario, I would suggest go for an OLEDB data driver route. You can find a training for the same here: Blue Prism® Guide to OLEDB

    If you go via this route, you can perform your manipulation over Results sheet and then read the Results sheet in a collection called [Results Data] then loop over it and use an Update query like UPDATE [Employees$] SET [Q1Q1] = '[Results Data.Q1Q2]',[Q3Q4] = '[Results Data.Q3Q4]' WHERE [Employee ID] = [Result Data.Employee ID]

    But yes prior using such a query you need to install the Microsoft Access Runtime Engine on your machine and also you need to create a connection string and connect to your excel file using the OLEDB business object.

    There are many threads which I have also replied to that you can refer for more details if you go via second route:

    I have a question
    OLEDB INSERT Statement
    Get number of cells in an excel
    SQL query for insert data into excel using OLEDB
    OLEDB Collection

    ------------------------------
    ----------------------------------
    Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

    Regards,
    Devneet Mohanty
    Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
    WonderBotz India Pvt. Ltd.
    Blue Prism Community MVP | Blue Prism 7x Certified Professional
    Website: https://devneet.github.io/
    Email: devneetmohanty07@gmail.com

    ----------------------------------
    ------------------------------



Welcome to Community Chat!

A space for non-technical chat, tips for using the Community, and news from the Blue Prism Community Team.

This is not a Community for technical or Product related discussion. For a list of available communities, please visit our site map.

Introduce YourselfCommunity Quick TipsWomen in RPA