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