Append range to a locked sheet but format url as a link

[farow31916]

Hi,

I’m using append range to paste my data into a preformatted sheet where certain areas are locked for editing. When i append the column with URLs (as string), and open the excel sheet in sharepoint (Excel cloud) it doesn’t format the URL as a link automatically, even if i have the range which contains the URL’s unlocked for editing. I have to click the cell and press enter, then it formats it as a link. I add the URL as a string (e.g. "https://test.com") in an add data row activity to a datatable which i append to my formatted sheet. I’ve tried giving different permissions to the locked sheet, but none of them seem to fix my issue.

Even pasting it as a hyperlink formula doesn’t work until i select the cell and press enter. It even shows the formula as containing an error, even though the formular works when i select the cell and press enter. I have excel in danish, but i’ve tried both with the comma and semicolon (danish) version of the hyperlink formula:

With comma: Assign URL = "HYPERLINK(""https://test.com"",""Link"")"
With semicolon: Assign URL = "HYPERLINK(""https://test.com"";""Link"")"

Comma version fails both before and after i edit the cell, semicolon fails before i click the cell, but works if i just select it and press enter. I have edited my excel settings to automatically format links and save the format sheet i append to, but it doesn’t seem to affect the cloud version. As you can see in the screenshot below, when i hover the cell with the hyperlink it says in danish “The formula in this cell contains errors.”. But as you can see in the bottom row, i’m able to make it function simply by selecting the cell value and pressing enter.

the cell value contains the following: =HYPERLINK("https://testlink.dk/";"Link")

[farow31916]

I realise now that I forgot to include the “=” sign in my assigns on this forum post, however my code does does contain them, it looks like this:

With comma: Assign URL = "=HYPERLINK(""https://test.com"",""Link"")"
With semicolon: Assign URL = "=HYPERLINK(""https://test.com"";""Link"")"

[Savan_S_Bangoria]

Hi @farow31916,

You are trying to write it in a predefined table due to which you are getting this issue, since the table has a predefined format it will not change it and it will write it as it is. Try writing it to another sheet where the format is not applied and check if it works.

[farow31916]

I’m aware of this, but this is the issue i’m trying to work around, it has to be a predefined table since there needs to be certain limits and format settings predefinded for different columns, and the process would be too slow if it had to manually set these settings by opening excel, which is why i’m appending to a preexisting sheet. If there’s no way to solve it on a predfined table then that’s how it is, but i’m hoping someone here knows a solution

[RajKumar_DC (RajKumar Durai)]

Hi @farow31916,

Greetings from the UiPath Forum!

Unfortunately, no, there is no way to make sure that the URLs are formatted as links when you append them to your Excel sheet. The only way to ensure that the URL’s are formatted as links is to manually select the cells and press enter after appending them. However, you may be able to find a workaround by using a macro to automate the process of selecting the cells and pressing enter.

Thanks,

[farow31916]

That’s what i feared But thank you for the answer, it’s nice to have confirmation that it isn’t possible.

[farow31916]

Turns out there is a way to do this. I fixed this using ClosedXML that’s already installed with the UiPath.Excel.Activities package. I simply import the namespace for ClosedXML and ClosedXML.Excel and add an invoke code activity as C# with the following code i wrote:

 var wb1 = new XLWorkbook(FilePath);
 var ws1 = wb1.Worksheet(1);
 var rows = ws1.RangeUsed().RowsUsed().Skip(1); // Skip header row
 
foreach (var row in rows)
//Go through all rows (except row 1).
{
    var rowNumber = row.RowNumber();
	var URL = ws1.Cell(ColumnLetter+rowNumber.ToString()).GetValue<string>(); 
//Column letter is the column where the data is located, i.e. if your link is in cells A2:A10 it would be "A"
 	ws1.Cell(ColumnLetter+rowNumber.ToString()).Value = "DisplayedLinkName";
	ws1.Cell(ColumnLetter+rowNumber.ToString()).Hyperlink = new XLHyperlink(URL, "HoverToolTip");
}

wb1.SaveAs(FilePath);