Excel - Copy and paste hyperlink formula without formula

Question

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);
            }

@Bassie edited my question to include the code I've tried. Thanks! — Dhinnesh Jeevan, Oct 30 '17 at 01:59

score 0 · Answer 1 · answered Oct 27 '17 at 09:16

0

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

answered Oct 27 '17 at 09:16

Frederik De Clercq

367
3
14

u got to be really drunk if you type text as tekst – matan justme Oct 23 '18 at 15:40

Karl Kristjansson · Answer 2 · 2017-10-27T09:59:35.023

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.

I can do this using C#, but unfortunately this is slow, which is why I use formula to generate the hyperlinks. If I were to format each cell to hyperlink, it will be damn slow :( — Dhinnesh Jeevan, Oct 30 '17 at 02:00

score 0 · Answer 3 · answered Jan 31 '23 at 08:43

0

Just had the same issue. Pasting the cells into blank word document and back again into desired worksheet did the job - links remained active and were pasted as values, without underlying formula with references.

answered Jan 31 '23 at 08:43

AndreasKri

23
5

Excel - Copy and paste hyperlink formula without formula

3 Answers3