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:29:39 GMT

I want to paste my filtered worksheet results from one worksheet back to the original filtered worksheet. For example, I filtered worksheet 1 and the S/N is from 20 to 40, I copied this result to another worksheet which creates a new S/N as 1 to 20 However, while copying the results back to the original worksheet I need it to paste the result to 20 to 40 exactly.

Re: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

michaeloneil — Mon, 20 Jun 2022 09:12:57 GMT

Hi

Can you tell me how you identify the range from the filtered collection? I'm assuming you used an action to tell you what rows to copy e.g. A20:C40. In this case I would use the same range to paste the values back for example if the identified the last cell with value being C40 and you used this as your end range then I would set the cell you want to paste into using a calculation as "A"&ToNumber(Mid("C40",2,10000))+1

This will remove the C from the cell address leaving 40 and this will be converted to a number and increased by 1 which will result in the new cell address then being A41. I hope this makes sense and was helpful.

Re: COPY AND PASTE RECODS TO A FILTERED WORKSHEET

devneetmohanty07 — Mon, 20 Jun 2022 21:58:42 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

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