COPY AND PASTE RECODS TO A FILTERED WORKSHEET

[OyinkansolaMusa]

COPY AND PASTE RECODS TO A FILTERED WORKSHEET?

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

[NileshJadhavBP]

Hi @Oyinkansola Musa,

You have to open file using Excel vbo and used "Go To Next Empty cell down" & "Go To Next Empty cell Right"  action to get the correct data.

after that need to use "Copy and paste worksheet range" action and need to pass the input parameters

Do let us know if you need any help

Thanks

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

[devneetmohanty07 · Accepted solution]

Hi @OyinkansolaMusa,

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

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