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
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
Or using variable
"=HYPERLINK(""" + variable + """)"
Hope this helps
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,
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.
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
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
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,
To make the values in column E clickable, you can use the “Hyperlink” activity in UiPath. Here are the steps to do so:
- Read the Excel file using the “Excel Application Scope” activity and specify the file path.
- Use the “Read Range” activity to read the data in the Excel file.
- Use a “For Each Row” activity to iterate through each row of the data.
- 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).
- 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.
- Save the Excel file using the “Excel Save Workbook” activity.
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?
Dim cell As Range
For Each cell In Selection If cell <> "E3:E24" Then ActiveSheet.Hyperlinks.Add cell, cell.Value End If Next cell
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,
Happen to try this?
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
While saving the url…say your url variable is
varurl then before writing it in place of
"=HYPERLINK(""" + varurl + """)"
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
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
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
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
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
I will try, thank you
Can you please help me with this approach , with steps and activity to paste data?
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
SampleCondition.xlsx (9.6 KB)