Read range: Invalid Hyperlink: Malformed URI is embedded as a hyperlink in the document

Learn
1

Hello all,

I currently having a constant error, I have an excel that contains passwords of users and on some occasions, the special characters used such as @ or . cause a hyperlink to appear.

I only need to read range the text in the cells but I keep getting the error message as above:
Read range: Invalid Hyperlink: Malformed URI is embedded as a hyperlink in the document.

Is there anyway to change the settings that it will ignore hyperlinks and just focus on the text in the document?

2

If the data in the sheet is not too large, please check the Preserve Format property in the Read Range activity. This should solve the URI issue because the whole sheet will be read as it is seen in the excel file.

1 Like
3

There are 3 options.

  1. Editing the format of the column to plain text (No recomended if you are no the FTE).

  2. Using excel aplicatiĂłn scope activity + read range (You need excel instaled).

  3. Correct the hyperlink with VB.net code (Invoke code activity), after the invoke code you can do the read range without problems.
    Note: you have to add this dependency Open XML PowerTools

    Dim strDoc As String
    strDoc = “pathofxls.xls”
    Dim txt As String
    Dim fixUri As Func (Of String,Uri) = Function (ByVal strNombre As String) As Uri
    strNombre.ToString
    Return New Uri(“http://broken-link/”)
    End Function
    Try
    Dim spreadsheetDocument As DocumentFormat.OpenXml.Packaging.SpreadsheetDocument = spreadsheetDocument.Open(strDoc,False)
    'If there are not bad links do nothing
    Catch ex As Exception
    If ex.ToString.Contains(“Invalid Hyperlink”)
    Dim fs As FileStream = New FileStream(strDoc, FileMode.OpenOrCreate, FileAccess.ReadWrite)
    OpenXmlPowerTools.UriFixer.FixInvalidUri(fs,fixUri)
    console.WriteLine(“Now you column is ok”)
    Else
    Throw New System.Exception(ex.Message)
    End If
    End Try

3 Likes
4

Open the sheet, ctr+a → right click → Remove Hyperlinks.
Run the BOT and it will run without this error.

10 Likes
5

2 nd option is also working fine.

6

Thank you that was instant :slight_smile: and its working .