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:
Read range – Whole Excel into a data table dtFullFiles
Build a new table called dtFilteredFiles
Use Filter Data Table activity where input is dtFullFiles and the Output stored in dtFilteredFiles
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?
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
@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!
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.
I had used the combination of excel + @balupad14 balareva activities.
The logic i had used here.
Read the excel file and store in dt1.
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.
(i had displayed as for ur reference).
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
after the extract hyperlink activity use write range activity to write the filtered dt1 into the sheet2.(filtered as per the step3)
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)
Wow… worked like a breeze. Thank you Nived. 2 things –
I used the Filter Data table instead of enumerable
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
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?