Move specific column to a specifed location in an Excel sheet

Hey guys,
I need to know if there is any method to move a specific column to a specific location in an Excel sheet.

Let’s say I wanna move column C to column A position and move the other columns, including column A to the right of the Excel sheet. Is there any activity for it ?

Thank you

1 Like

I am not sure if there are any specific activities as such.
You might have to automate the manual steps that you do to swap those columns on the excel application, or if you are reading them anyway using RPA, then you can add some datatable manipulation activities to swap these columns before writing them again.

Hope this makes sense. Let me know if you need any help with either f this.

1 Like

Hi @NiranjanKN

There no specific activities in UiPath to achieve this. However as suggested by @kaderms you can read excel data into datatable, perform column swapping and write the data back to an excel file.

1 Like

You can do that using VB code :smiley: . You can do that using the data table as well.

  1. Read the entire excel and store in data table.
  2. Then use set ordinal which is available to move the columns to required position
  3. Write it back to excel

Please let me know if you are interested in VB code for this. Happy to share :slight_smile: @NiranjanKN

2 Likes

Hey, @HareeshMR @kaderms
I used the manual method, but all processes are carried out, but at the end the Final file won’t be saved.

And, to use by reading entire excel into a datatable, I didn’t find the set ordinal activity.

Thank you.

1 Like

Here is the reference formula for set ordinal

dt.Columns(column to be moved).SetOrdinal(column number to which the column to be moved)

1 Like

Hey, @HareeshMR

In this test file Test.xlsx (39.5 KB),
I need to filter the MST column by blanks, then move the BILT column to position of MATN, and then convert that entire BILT column to numbers.

Thank you.

1 Like

For this entire process, record the macro @NiranjanKN, then make it dynamic so that you can use it further as well

Dynamic in the sense, if the column BILT is at a specific and fixed column number, then it is not required else you need to make it dynamic

1 Like

Hey, @HareeshMR @aksh1yadav
Can you just share a code to execute the test file.

Thank you

1 Like

Use Invoke method for set ordinal
image

image

Target Object will be
Datatable…Columns(“columnname”)

2 Likes

I’m still getting an error in this method.

Thank you.

1 Like

Use write range after performing those moving column actions(invoke methods)
test Flow.zip (18.6 KB)

1 Like

Its still not working.

Thank you.

1 Like

Its working fine…with

1 Like

test Flow (2).zip (19.0 KB)
You just run this script it will give u the desired output

1 Like

@ImPratham45 @HareeshMR @kaderms
I tried the same code for my sample file and I’m getting an error that Value cannot be null.

Thank you.

1 Like

can you share your project folder

1 Like

Got it bro
The column name is : "BILT "
there are empty spaces aftre BILT
PLS REMOVE THOSE FROM excel file or add those in xmal workflow file in project

1 Like

Hey,
I got it.Move Column.zip (59.2 KB)

Thank you.