topic RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET in Product Forum

COPY AND PASTE RECODS TO A FILTERED WORKSHEET

musaoyinkan — Wed, 15 Jun 2022 10:35:00 GMT

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

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

NileshJadhavBP — Mon, 20 Jun 2022 13:34:00 GMT

You can achieve this using BP code stage

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

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

musaoyinkan — Mon, 20 Jun 2022 14:24:00 GMT

@Nilesh Jadhav how do i go about this?

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

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

devneetmohanty07 — Mon, 20 Jun 2022 22:39:00 GMT

Hi @musaoyinkan,

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

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

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

musaoyinkan — Tue, 21 Jun 2022 09:30:00 GMT

@devneetmohanty07
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
------------------------------

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

devneetmohanty07 — Tue, 21 Jun 2022 09:34:00 GMT

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

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

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

musaoyinkan — Tue, 21 Jun 2022 09:43:00 GMT

@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
------------------------------

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

devneetmohanty07 — Tue, 21 Jun 2022 09:59:00 GMT

@musaoyinkan,

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

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

RE: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

NileshJadhavBP — Thu, 10 Nov 2022 20:26:00 GMT

Hi @Oyinkansola Musa,

Sorry for late reply, hope your issue is resolve.

Thanks
Nilesh

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