Need help on making existing folderpaths in .xls column as clickable in uipath

Help Community
,
1

Hello all, I have a project where I need to generate custome logfile with current date and time which will capture screenshots during execution and append screen shot urls with current date and time . I was able to create execution log file create successfully in .xls. It has screenshot urls column with file paths in column E from E3 to E24. I need help, how to make those values as clickable. Can some one please help and guide me through steps

2

@r0818

Welcome to the community…instead of giving the link directly give like this in your write cell or in datatable which you will writr to exce

"=HYPERLINK(""YourLinkAsString"")"

Or using variable

"=HYPERLINK(""" + variable + """)"

Hope this helps

Cheers

1 Like
3

Hi @r0818, welcome to the Community.

You can try executing a macro for this:

Sub ConvertRangeToHyperlink()
    Dim cell As Range
    
    For Each cell In Selection
        If cell <> "" Then
            ActiveSheet.Hyperlinks.Add cell, cell.Value
        End If
    Next cell
End Sub

Hope this helps,
Best Regards.

1 Like
4

hi Thanks for responding, screenshots are dynamically adding while execution , pls check this screenshot for reference . and sheet name also not static, its like ExecutionLogs_currentdate and time dynamically will change everytime bot run. Need help to make E column folderpaths as clickable.

image

5

Can you please give detailed steps of activities to run this macro for how to do this for column E, E3 to E24? Thank you so much

6

After converting Column E values as clickable then need to attach this logfile to send mail to client. Can you please convert E column values from E3 to E24 as clickable in .xls

7

@r0818

Replace the ‘Selection’ with Range(“E3:E24”) in the above macro & save the entire thing in a text file. In the workflow, take Invoke VBA activity & set the input file path of this text file. Specify the entry method as ‘ ConvertRangeToHyperlink’ & run the code.

Hope this helps,
Best Regards.

9

To make the values in column E clickable, you can use the “Hyperlink” activity in UiPath. Here are the steps to do so:

  1. Read the Excel file using the “Excel Application Scope” activity and specify the file path.
  2. Use the “Read Range” activity to read the data in the Excel file.
  3. Use a “For Each Row” activity to iterate through each row of the data.
  4. Use the “Hyperlink” activity to create a clickable hyperlink for each value in column E.
  • In the “URL” field, enter the file path for the screenshot.
  • In the “Display Name” field, enter the value from column E.
  • In the “Selector” field, indicate the cell where the hyperlink should be placed (e.g., “E” + row.ToString).
  1. Use the “Write Cell” activity to write the hyperlink into the Excel file.
  • In the “Cell” field, indicate the cell where the hyperlink was placed.
  • In the “Value” field, enter the hyperlink activity variable.
  1. Save the Excel file using the “Excel Save Workbook” activity.
10

Hello , I tried with below macro as you provided and ran workflow… but it did not convert to hyperlink. can you please help if any wrong?
Sub ConvertRangeToHyperlink()
Dim cell As Range

For Each cell In Selection
    If cell <> "E3:E24" Then
        ActiveSheet.Hyperlinks.Add cell, cell.Value
    End If
Next cell

End Sub

12

@r0818

I believe I suggested you to replace the ‘Selection’ part with the required range. Please try this:

Sub ConvertRangeToHyperlink()
    Dim cell As Range
    
    For Each cell In Range("E3:E24")
        If cell <> "" Then
            ActiveSheet.Hyperlinks.Add cell, cell.Value
        End If
    Next cell
End Sub

Hope this helps,
Best Regards.

13

@r0818

Happen to try this?

Cheers

14

Hi Anil, yes… its working when I change each cell values passing within the formula. My scenario , need to convert all screenshot urls catured during runtime and saved into xls to be enabled as clickable

15

@r0818

While saving the url…say your url variable is varurl then before writing it in place of varurl use "=HYPERLINK(""" + varurl + """)"

Cheers

1 Like
16

but while writing from dt to CSV, hyperlinks not taking as csv not support. so copied from CSV to xls and then trying to make url column as clickable. Any help would be appreciated

17

@r0818

Yes while writing to excel…

You would have read the csv into datatable…use a add data column and add a nee column say newcol

Now use assign as below

Dt.Columns("NewCol").Expression = "'=HYPERLINK(""'" + [ColumnContainingLink] + "'"")'"

Then use delete column and delete columncontaininglink

Then you have a column with all hyperlinks

Hope this helps

Cheers

18

yes, its working… Thank you so much! how can I pass custom display text for url and also need help to highlight "Pass " as green color in status column of data table add row
display test in blue color for url, pass as green color and fail as red color. Can you please help

19

@r0818

For this I would suggest you to use conditional formatting on excel…

You can create a template file with the formatting on it already and paste the data…

So once data is pasted…As conditional formatting rules are set already …the cell colors would change to red or green depending on pass or fail

Or

You can use format cells activity and format each row or cell by reading the data again and using a for loop and check the status

And to add custom text try this

Dt.Columns("NewCol").Expression = "'=HYPERLINK(""'" + [ColumnContainingLink] + "'"",""Pass/Fail"")'"

Add pass or fail as per your need

Cheers

1 Like
20

I will try, thank you

Cheers

1 Like
21

Can you please help me with this approach , with steps and activity to paste data?

22

@r0818

Basically you would use a write range only when I say paste…SO you can use write range workbook or write range in excel for that

And Conditional Formatting is to be done on an Predefined excel so that you have the formatting already in place and when data is written it is updated

image
image970×427 22.5 KB

SampleCondition.xlsx (9.6 KB)

cheers