Last Column Index in an Excel where the columns are dynamic

[Sonadri_Tanaya_Mishra_EXT (Sonadri Tanaya Mishra)]

Want to get the last column of the excel,(the last Col3 refer the attched image) where the column count is not constant. I cant read the data to a datatable because the column headers are not unique and i dont want to disturb them as well.

[ashokkarale]

@Sonadri_Tanaya_Mishra_EXT,

Refer this solution.

Find last column in excel?

I want find last column ( A,B,C,…) and assign last row & row 1 in range as below. [image] Please guide me about it.

[Anil_G (Anil Gorthi)]

@Sonadri_Tanaya_Mishra_EXT

inside use excel file use the below

Excel.Sheet("Sheetname").GetNumberOfColumns this will give count of columns or the index of last column…if you want zero based then subtract 1

then UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(Excel.Sheet("Sheetname").GetNumberOfColumns) will give the excel column name like B D E etc

cheers

[Sonadri_Tanaya_Mishra_EXT (Sonadri Tanaya Mishra)]

Hey Anil,

Thanks for the response. I am not using excel scope, i am using Workbook activity, there can use?
Thanks,Sonadri

[Anil_G (Anil Gorthi)]

@Sonadri_Tanaya_Mishra_EXT

then you need to use read rnage and then count the columns…while reading just uncheck add headers so that you wont have any trouble reading

cheers

[V_Roboto_V]

Hi @Sonadri_Tanaya_Mishra_EXT

Here is a solution:

STEP 1:
Use Read Range workbook. But disable Add Headers

The output will be:

STEP 2:
Use the following Assign Statement to get the last column

dt_LastColumn = dt_LastColumn.DefaultView.ToTable(False, dt_LastColumn.Columns(dt_LastColumn.Columns.Count - 1).ColumnName)

The output will be:

STEP 3:
Use the Invoke Code activity to Replace the Column name with First row, and then remove first row.

The Code:

For i As Integer = 0 To dataTable.Columns.Count - 1
    dataTable.Columns(i).ColumnName = dataTable.Rows(0)(i).ToString()
Next

dataTable.Rows.RemoveAt(0)

The Argument:

THE FINAL OUTPUT

If this solves your issue, Do mark it as a solution.

Happy Automation

[Sonadri_Tanaya_Mishra_EXT (Sonadri Tanaya Mishra)]

Once i get the index, can I get the character! like if idx is 26 then Z is the column, i need it for range

Thanks

[ashokkarale]

@Sonadri_Tanaya_Mishra_EXT,

This code will give you letter from index

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(26) 

[Sonadri_Tanaya_Mishra_EXT (Sonadri Tanaya Mishra)]

Thank you so much for the in detailed explanation.

[Anil_G (Anil Gorthi)]

@Sonadri_Tanaya_Mishra_EXT

@Anil_G

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(Excel.Sheet("Sheetname").GetNumberOfColumns)

Yes you can using the same formula above

Cheers