Convert Excel Cell Values to Clickable Hyperlinks

[Vaishnavi_R]

Hi Team,

I have a DataTable DT1 with two columns: URL and Keyword, and a Master Sheet with the same columns.

I need to:

1. Append the rows from DT1 to the Master Sheet.
2. Format only the newly appended rows by:

• Converting the text in the URL column to clickable hyperlinks.
• Applying red font color to both URL and Keyword columns for the appended rows.

Could you please guide me on how to implement this in UiPath?

[sonaliaggarwal47 (Sonali Aggarwal)]

@Vaishnavi_R

Converting the text in the URL column to clickable hyperlinks.

hi @Vaishnavi_R

you can refer below thread on same.

Hyperlink the Link present in Datatable

@Goku You can add a formula like this to each row using expression Dt.Columns("HotelNameUrl").Expression = "'HYPERLINK(""' + [HotelNameURL] + '"",""Link"")" Cheers

Dt.Columns(“Url”).Expression = “'HYPERLINK(”“’ + [URL] + '”“,”“Link”“)”

for changing the font color, refer below thread:

How to change the font color in the excel cell

Hi @Vrushali_Gave , Please refer below article it night help you Thanks!

Hope this helps.

Regards
Sonali

[sharazkm32 (Sharaz K M)]

Try below steps

1. Open Excel File
2. Identify the last row in the excel file using Find First/Last Data Row activity
3. Loop through the data table
4. Add count with the last row
5. Use write cell activity
6. Use formula “=HYPERLINK(”“” & CurrentRow(“Column1”).ToString & “”“, “”” & CurrentRow(“Column1”).ToString & “”“)” to convert the string to hyper link
7. Refer below screenshot for code details

Also attaching code for your reference

EmailScenarios.zip (143.8 KB)

[Vaishnavi_R]

Thanks You. I used your workflow ,it’s successfully converting the link into a clickable URL. But, after I added the Format Cell activity to change the font color to red, the hyperlink is reverting back to plain text.

Do you have any idea why this might be happening?

[sharazkm32 (Sharaz K M)]

Hi Vaishnavi,

Need to check on that. Is it mandatory to change the links to red color or it it okay to change the cell color as red

Normally links are maintained in the same format only

just checking to see the importance of requirement

[Vaishnavi_R]

As I am appending data to the master file, it is necessary to change the cell color of columns A and B to red for better readability.

[sharazkm32 (Sharaz K M)]

In that case- It is better to use VBA for changing the font color. Format cells might change the existing properties of the cell

Use the updated file where i have created the VBA file and included invoking the same as well

EmailScenarios.zip (148.5 KB)