topic RE: Swap | Interchange | Move Columns in an Excel in Product Forum

Swap | Interchange | Move Columns in an Excel

vinodchinthakin — Tue, 10 Jan 2023 15:48:00 GMT

Hi,

I have an Excel having data ranging from columns A to Z, I have following requirements.

1. Column G along with values to be inserted/moved after Column B.
2. Column E and Column F needs to swap their positions.
3. Needs to delete Columns P, S, and W.

Do we have any specific actions for the above.

Thanks!

------------------------------
vinod chinthakindi
------------------------------

RE: Swap | Interchange | Move Columns in an Excel

shashank.kumar280 — Wed, 11 Jan 2023 04:19:00 GMT

Here is the code stage that you can use for deleting columns

Dim wb, ws, excel, sheet As Object

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()

excel = ws.Application
sheet = excel.ActiveSheet

ws.Columns(Range).EntireColumn.Delete

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Swap | Interchange | Move Columns in an Excel

PvD_SE — Wed, 11 Jan 2023 10:53:00 GMT

Hi Vinod,

There's a collection action 'Get column from collection with header'. It lets you extract a full column from a collection to a new collection. Another action is 'Add column', that lets you add and existing column from a collection to a new collection. The thing to do is use either action on all each column in the order you'd like them to show up in your new collection.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: Swap | Interchange | Move Columns in an Excel

vinodchinthakin — Wed, 11 Jan 2023 14:16:00 GMT

Hi Paul, Thanks for the solution. But I don't want to read excel data to collection. I want to Sort Columns positions within Excel.

------------------------------
vinod chinthakindi
------------------------------

RE: Swap | Interchange | Move Columns in an Excel

vinodchinthakin — Wed, 11 Jan 2023 14:19:00 GMT

Hi @shashank.kumar280 Thanks for the solution. For deleting multiple columns, how to provide range value.? For example I want to delete Columns P, S, and W. in a single go instead of calling delete column action thrice!

------------------------------
vinod chinthakindi
------------------------------

RE: Swap | Interchange | Move Columns in an Excel

PvD_SE — Thu, 12 Jan 2023 06:56:00 GMT

Hi Vinod,

In that case there's always the 'Copy and Paste Worksheet Range' action. It is and out-of-the-box action in the BP VBO for Excel and if you can trust the help-text (of which there is none) it might just do exactly what you want.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------