Retrieve hyperlink from a value in a cell in Excel

Hello,

I have an excel which contains various documents A.doc, B.pdf,C.rtf, etc. All these are stored in excel as a hyperlink to the file path. For eg. A.doc will be the text displayed in excel and the hyperlink will be ("C:\documents\Files\A.doc). There are 100 such documents in the folder, so a hundred such values(with their respective hyperlinks) in the excel column name(“FileName”)

In my program, I use specific conditions to extract certain documents only. I am doing the foll steps:

  1. Read range – Whole Excel into a data table dtFullFiles
  2. Build a new table called dtFilteredFiles
  3. Use Filter Data Table activity where input is dtFullFiles and the Output stored in dtFilteredFiles
  4. I use WriteRange to add the values of dtFilteredFiles into a new Excel

The issue I am facing is that only the filenames have got extracted to the new excel. How do I ensure that the respective hyperlinks are also transferred?

Kindly guide.

Thanks!

Hi @Purnima_Sambasivan

What condition did u put in filter datatable activity

where skill =‘HTML’ or skill=‘Java’ . The ‘HTML’ and ‘Java’ keywords is depended on the user input and skill is another column name

Can u Share the screenshot of that filter activity?

where arrtemp(0), arrTemp(1), arrtemp(2) all are skills.

Did u put anything in output columns section?

Yes, I have put it. Please refer to the picture

@NIVED_NAMBIAR . The column DocName needs to have the link

Hi @Purnima_Sambasivan,

Here is an activity called extract hyperlink from BalaReva.EasyExcel package . it extracts the hyperlink , display and colinfo.

Input format :

image

Sample :

if you know the cell range , you can give like this “A2:A4”

Output :slight_smile:

image

ExtractHyperLink.zip (39.7 KB)

Thank you
Balamurugan.S

Hi @Purnima_Sambasivan check this below.

I had used linq query here

text_file.zip (1.4 MB)

Hope it helps

Regards

Nived N

Happy Automation

Hello Sir, thank you so much for this. I use your products regularly and it has helped me solve many things, especially wrt excel graphs. For Hyperlink also I have used it.

For the example you have given, I need the same contents copied to another sheet (along with the hyperlink). When I do that only the text is getting copied and not the hyperlink (Please note I don’t need the hyperlink as a separate column)

When I copy the DocName column into another sheet, only the name is getting transferred and not the hyperlink. Hope I am clear

Thanks for the response, Sir.

@NIVED_NAMBIAR Thank you for the guidance. I think I was not clear in my question. Please refer to my comments above and suggest solution. Thanks again!

Hi @Purnima_Sambasivan,

Your requirement is very interesting.

As of my understanding,

  1. I think you need to use the above mentioned balareva package, to get the links addresses first.
  2. Then write the links to the output file as normal text.
  3. Now need to perform UI Automation on the output file, to make normal text form of links to hyper links.

Thanks and Regards.

Hi @Purnima_Sambasivan,

It may help you…
There is an activity called “Add Hyperlink from Datatable” from BalaReve.Excel package.

Here I have attached the sample that it copies (Sheet1) the hyperlink data into a datatable. After It will transform the data table to a hyperlink source and write in the excel sheet2.

Here is the sample :
ExtractHyperLink.zip (41.0 KB)

Final output from sheet2:
image

Thank you
Balamurugan.S

Hi @Purnima_Sambasivan

I had tried your query and had got it.

I had used the combination of excel + @balupad14 balareva activities.

The logic i had used here.

  1. Read the excel file and store in dt1.

  2. use extract hyperlink activity from balareva easy excel activity and extract the hyperlink and stored in a datatable dt2 which contains Display text, url, cell address as column .
    The dt2 is like as below.
    image (i had displayed as for ur reference).

  3. Filter the datatable dt1 as per the below linq query ( I had filtered for skills either java or HTML for demonstartion)

dt1 = dt1.AsEnumerable().Where(Function(r) r(“Skill”).ToString.Equals(“Java”) or r(“Skill”).ToString.Equals(“HTML”)).CopyToDataTable

  1. after the extract hyperlink activity use write range activity to write the filtered dt1 into the sheet2.(filtered as per the step3)

  2. Then loop through each row in filtered dt1 using for each row and find find corresponding url in dt2 using lookup datatable activity and add that link to the display text using Insert Hyperlink activity (from Balareva.EasyExcel Activities)

This is the entire logic i applied.

So i had used input file as below
image

Output i got

image

Check the attached workflow

text_file.zip (1.4 MB)

Hope it helps

Thanks for this nice question

Thanks @balupad14 for this nice excel actvity.

Nived N

Happy Automation

1 Like

Wow… worked like a breeze. Thank you Nived. 2 things –

  1. I used the Filter Data table instead of enumerable
  2. The process of just getting a link in a round about way is slowing down my process. Wish there was a way where I can directly transfer the URLs also from the excel when we transfer the contents from the excel to the data table

Really grateful for the guidance.

Bala Sir, this is an amazing feature. Thank you so much. Solved most of my problem but for this. When I send this excel via email to someone and they download it, it is giving a relative path to the document instead of the absolute path.

For eg. If Revathi.doc is an hyperlink to the D:\UiPath\Documents\Revathi.doc, then when any user downloads this excel from their machine, this is getting converted to the link C:\Users\Uipath\Documents\Revathi.doc and when I click on it , it says “Specified file is not found”. I would like to retain the absolute path. Any suggestions for it?

Thanks Sir!

1 Like

Hi @Purnima_Sambasivan

Another way would be invoking the c# code in Uipath which does these

Thank you @Purnima_Sambasivan. I noted the point. in future releases, I will add this one. ~

Thank you
Balamurugan.S

1 Like

Wow, thank you Sir, that would be so useful!

1 Like