Move specific column to a specifed location in an Excel sheet

Help
, ,
1

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
2

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
3

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
4

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
5

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
6

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
7

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
8

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
9

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

Thank you

1 Like
10

Use Invoke method for set ordinal
image

image

image
image698×195 6.92 KB

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

2 Likes
11

I’m still getting an error in this method.

Thank you.

1 Like
12

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

1 Like
13

Its still not working.

Thank you.

1 Like
14

Its working fine…with

1 Like
15

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

1 Like
16

@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
17

can you share your project folder

1 Like
18

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
19

image
image785×185 5.72 KB

20

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

Thank you.

1 Like