Excel - Read hyperlinks embedded in an excel (without external library)

Hi,

Please help me read the hyperlinks embedded in an excel file without using external libraries.
I have found several threads on the forum and for most of the BalaReva library helped them out but in my organization, I cannot refer to external libraries.

So possibly using invoke code/formulas, excel workaround etc. Appreciate the help.

Please see attachment,
ExcelHyperlinkTest.zip (14.9 KB)
hyperlinks.xlsx (9.1 KB)
I have tried using Excel Cell Formula, Read Range, Read Cell and nothing seems to be working for this.
Thanks.

To read the hyperlinks embedded in an Excel file in UiPath without using external libraries, you can utilize the built-in activities and functions available in UiPath. Here’s a step-by-step approach to achieve this:

  1. Use the Read Range activity to read the Excel file into a DataTable. Provide the path of the Excel file as the input.

  2. Iterate through each row in the DataTable using a For Each Row activity.

  3. Within the loop, access the value of each cell containing a hyperlink using the item array variable (row.Item("ColumnName")). Make sure to replace “ColumnName” with the appropriate column name or index containing the hyperlinks.

  4. Extract the hyperlink from the cell value by using string manipulation techniques. The hyperlink format in Excel typically appears as “display_text - hyperlink_address”. You can split the cell value based on the hyphen (“-”) and retrieve the second part, which represents the hyperlink address.

Here’s an example of how you can implement this logic using UiPath activities:

Read Range:
- Input: Path to the Excel file
- Output: DataTable (let's assume it's named "dt")

For Each Row:
- Input: dt

   Assign activity:
   - To: hyperlink
   - Value: row.Item("ColumnName").ToString()  // Replace "ColumnName" with the actual column name or index containing the hyperlinks

   Assign activity:
   - To: hyperlinkAddress
   - Value: hyperlink.Split({" - "}, StringSplitOptions.None)(1)

   // Perform further operations with the extracted hyperlink address

In the above example, the hyperlink address is stored in the hyperlinkAddress variable, which you can use for further processing or logging.

Note that this method assumes a specific format for the hyperlinks in Excel, where the display text and hyperlink address are separated by a hyphen. Adjust the string manipulation logic accordingly if your Excel file has a different hyperlink format.

By following this approach, you can extract the hyperlinks embedded in an Excel file in UiPath without relying on external libraries.

Regards JJ

Hi @lashie , thanks for your response on my query above.

I have tried the suggested approach but it doesn’t show the embedded links inside.

In the context above, this doesn’t display the links:

Assign activity:

  • To: hyperlink
  • Value: row.Item(“ColumnName”).ToString() // Replace “ColumnName” with the actual column name or index containing the hyperlinks

I have attached my code and also example file along with my question above, did you get a chance to try yourself? I will appreciate if you can try it with your approach and share the code.

Thanks!

Hi @harpreet

Try this using Invoke Code Activity

Imports System
Imports System.Data
Imports System.Linq

Public Sub ReadHyperlinksFromExcel()
    ' Provide the path of your Excel file
    Dim excelFilePath As String = "C:\Path\To\Your\Excel\File.xlsx"
    
    ' Load the Excel file
    Dim excelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    Dim workbook As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Open(excelFilePath)
    
    ' Get the first worksheet
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Sheets(1)
    
    ' Get the used range of the worksheet
    Dim usedRange As Microsoft.Office.Interop.Excel.Range = worksheet.UsedRange
    
    ' Get all the hyperlinks in the used range
    Dim hyperlinks As Microsoft.Office.Interop.Excel.Hyperlinks = usedRange.Hyperlinks
    
    ' Iterate over each hyperlink and print its address
    For Each hyperlink As Microsoft.Office.Interop.Excel.Hyperlink In hyperlinks
        Dim address As String = hyperlink.Address
        Console.WriteLine("Hyperlink: " + address)
    Next
    
    ' Close the workbook and release the resources
    workbook.Close(False)
    excelApp.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
End Sub

I hope it works!