Insert Column at the last

[Vishal_Kumar4 (Vishal Kumar)]

Using Insert Column UiPath activity, how to insert new column automatically at the end of used range?

[mkankatala (Mahesh Kankatala)]

Hi @Vishal_Kumar4

In the Relative to Column field give

Excel.Sheet("CY1").Datatable value.Columns.Last.toString

In the Range give Excel.Sheet(“CY1”)
In the Add header field give new column name to add at last.
Keep after option in dropdown.

Hope it helps!!

[Vishal_Kumar4 (Vishal Kumar)]

[arivu96 (Arivazhagan A)]

Add a “Read Range” activity to read the entire sheet into a DataTable (e.g., dtData).

Get the last column index using:

lastColumnIndex = dtData.Columns.Count + 1

[Vishal_Kumar4 (Vishal Kumar)]

Read range takes lot of time as the sheet has over 30K records

[mkankatala (Mahesh Kankatala)]

Try the below expression,

Excel.Sheet("CY1").Datatablevalue.rows(0)(Excel.Sheet("CY1").Datatablevalud.Columns.Count-1)

Hope it helps!!

[Vishal_Kumar4 (Vishal Kumar)]

[mkankatala (Mahesh Kankatala)]

Give .toString at last or use the below expression,

Excel.Sheet("CY1").Datatablevalue.rows(0)(Excel.Sheet("CY1").Datatablevalud.Columns.Count-1).toString

[sharazkm32 (Sharaz K M)]

To get the last column - Considering the file size - I would suggest you to use VBA for the same. I am attaching the VBA file which you could easily invoke from your workflow. The output will give you the last column name. You can use this column name to add a column next to it

GetLastColumn.txt (260 Bytes)

[Anil_G (Anil Gorthi)]

@Vishal_Kumar4

1. First use a read cell activity with range as Excel.Sheet("SheetName.Range(UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(Excel.Sheet("Sheetname").GetNumberOfColumns) + "1")
2. In the range field on insert activity give only till sheet
3. In column name field give output of read cell from step 1

Cheers