Extracting Data from Excel using Column Alphabet instead

[JasonJC]

Extract Data.zip (878.5 KB)
Attached is sample of data. My original data have more than 10 workbooks to combine and the number of data in each workbook that needs to be extracted can be 500-2000 rows. Currently I am extracting the data by using input dialog to specify which column to extract the data and merged together. The problems with the file is there might be missing data in between the rows as highlighted in the excel files. The empty rows need to be kept there. The starting row of each data can be different, the worksheet name to extract the data is different for each workbook. The header name might not be the same throughout all files. The column to extract data from is not the same throughout the workbooks. I have created a workflow that works in extracting the data. However, the actual data to be merged together is 20,000+. By using the current workflow I created is still time consuming. And chance of error in keying wrong number for the column number is high. It is hard to speed up this process. But I just need help if there is a way to extract the data by typing in the column alphabet for example column B from excel to be “B” into the input dialog instead of keying in “2”. I would like to just reduce the chance of error. For example if column goes until “AD”, might calculate wrongly and keyed in wrong column number, it will be tedious to repeat the whole process.

[Yoichi]

Hi,

But I just need help if there is a way to extract the data by typing in the column alphabet for example column B from excel to be “B” into the input dialog instead of keying in “2”.

Can you try to use the following expression?

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnLetterToColumnIndex(ColumnLetter) 

ConvertColumnLetterToColumnIndex method returns index number of the column like R1C1 style.
For example, if you set "B" in the above expression, it returns "2".

Regards,

[JasonJC]

Thanks alot for your help, it solved my issue.