Excel - Copy and paste hyperlink formula without formula

0
votes

I have an Excel add-in which will create a table, and one of the columns has this HYPERLINK formula:

=HYPERLINK(CONCATENATE("https://www.example.org/",[id]),[id])

When users copy the cells which contains this formula, and they paste it into another worksheet, they get this:

=HYPERLINK(CONCATENATE("https://www.example.org/",ListObject1[id]),ListObject1[id])

So, let's say they copy a cell from the first row of the first worksheet, and paste it to the 5th row of another worksheet, they will get the value of the 5th row of the first worksheet due to the referencing of the formula.

Unfortunately this is not intended. What users expect is to get the value they copied, instead of the formula. So, what can I do to make sure that when users perform the paste operation, they will get the hyperlink value instead of the formula?

Unfortunately "Paste as Values" isn't working because we will lose the hyperlink.

Now, I'm trying to research if there's any way I can edit the Clipboard data to remove the formula and insert the hyperlink value in the Clipboard data, so that when users do the paste, they will get the hyperlink data.

So far it's not working. I modified the "XML Spreadsheet" in the Clipboard data object, but it didn't work, probably there's some other things I need to modify.

I appreciate any help or suggestions to solve this issue.

Thanks!

Edit:

Here's the code I've tried to remove the "ss:Formula" from the "XML Spreadsheet" in Clipboard data. The "ss:Formula" stores the cell formula, so I thought that if I remove it, user will get the exact value when pasting instead of getting the formula.

            var dataObject = Clipboard.GetDataObject();
            var newDo = new DataObject();
            foreach (var format in dataObject.GetFormats())
            {
                try
                {
                    var value = dataObject.GetData(format);
                    newDo.SetData(format, value);
                }
                catch (Exception e)
                {
                   Logger.Debug(e);
                }
            }

            var xmlSpreadsheetClipboardData = Clipboard.GetData("XML Spreadsheet");
            if (xmlSpreadsheetClipboardData != null)
            {
                // Read the data and extract the workbook name, worksheet name, and cell.
                var memoryStream = (MemoryStream) xmlSpreadsheetClipboardData;
                var reader = new StreamReader(memoryStream, new System.Text.UTF7Encoding(), true);
                var xml = reader.ReadToEnd().Replace("\0", "");
                var xmlDoc = new XmlDocument();
                xmlDoc.LoadXml(xml);
                var node = xmlDoc.SelectSingleNode("/Workbook/Worksheet/Table");
                var worksheetXmlElementList = xmlDoc.GetElementsByTagName("Worksheet");
                var worksheetXmlElement = worksheetXmlElementList[0] as XmlElement;
                var tableXmlElement = worksheetXmlElement.GetElementsByTagName("Table")[0] as XmlElement;
                var rowXmlElement = tableXmlElement.GetElementsByTagName("Row")[0] as XmlElement;
                var cellXmlElement = rowXmlElement.GetElementsByTagName("Cell")[0] as XmlElement;
                cellXmlElement.Attributes.RemoveNamedItem("ss:Formula");
                newDo.SetData("XML Spreadsheet", xmlDoc.InnerXml);
                Clipboard.SetDataObject(newDo);
            }
2
c#excelhyperlinkclipboardclipboarddata
Please share the code that you have tried – Bassie
@Bassie edited my question to include the code I've tried. Thanks! – Dhinnesh Jeevan

2 Answers

0
votes

This is the way how Excel is build. If you want to overrule that you can copy a formula by pressing ctrl C. And the place where you want to paste it press ctr+alt+V than select only tekst.

If you want to solve it in C# Maybe u can set the row invisible with the function in it. And show the value of this invisible record asside it. If you want to copy a value in excel use somthing like: https://stackoverflow.com/a/24555571/5713884

0
votes

If you can create a new column, then you could use VBA to convert the link to a url which you can then copy.

Sub ExtractHL()
Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
        HL.Range.Offset(0, 1).Value = HL.Address
    Next
End Sub

https://answers.microsoft.com/en-us/office/forum/office_2007-excel/remove-friendlyname-from-hyperlink-via-function-or/3ccec10d-d7cc-4c56-a9a3-9e13aeda77e1

Or you could skip the second column plan and just using offset(0,0) and replace the link in the current cell with the URL. This macro could be run by a button and perhaps you would edit it to copy/paste the URL to the other sheet for the user, and leave the link in its original location.

I think VBA is your only solution if using Excel only.