0

When I copy a cell, what includes a hyperlink formula, it is insert as a html link as formatted by the formula. That only works as long as the receiving applications is not another Excel 2010 instance/application object.
When I copy it from the origin instance to another instance, it copies only plain text.

Can I write code what put a html formatted text in the clipboard instead of Excel cell content object?

Community
  • 1
  • 1
Stefan
  • 372
  • 1
  • 16
  • 1
    Please share the code that you're currently using. – Cyril Aug 29 '17 at 16:27
  • it is not really a code just a formula `=HYPERLINK(E10;C2 & "_" & C8 & "_" & TEXT(C4;"000") )`. I do copy (origin doc) / paste (different instance). I played a bit with code but without any success so far. – Stefan Aug 30 '17 at 06:43
  • Please show what you've tried. I am not seeing an issue with simply copy/pasting links between Excel document, provided you are appropriately hyperlinking. I would suggest creating the hyperlink in VBA so the output in the cell is just a hyperlink, not a formula. That should fix the issue (e.g., range("name").formula=application.hyperlink(blah)). – Cyril Aug 30 '17 at 13:32
  • try to copy from one instance to another instance of Excel, than you see it. When you open the task manager there have to be twice EXCEL.EXE – Stefan Aug 30 '17 at 16:41
  • My suggestion would still stand. Don't allow the cell itself to say the formula, just the actual hyperlink. There's a disconnect between a formula versus an actual hyperlink, where the formula is a single string, but the actual hyperlink is an array with both a destination and a label. – Cyril Aug 30 '17 at 17:19
  • I'm sorry but I don't really understand what you mean. All what I have is cell containing this formula: `=HYPERLINK(E10;C2 & "_" & C8 & "_" & TEXT(C4;"000") )`. All what I do, is to copy it from one instance of Excel 2010 to another one (from one Excel 2010 window to another one). The result is that only the description/text/label of the hyperlink is inserted. – Stefan Aug 31 '17 at 07:41
  • @Cyril I add some gifs, hope that explains it well – Stefan Sep 04 '17 at 07:41

2 Answers2

0

There is a work around what solves the Excel to Excel copy problem but now I have a problem when pasting it in any other application

Based on the following posts I adapted the code mentioned there for VBA. The problem is now, that Windows now pastes the plain text in other application but it works flawless in Excel. I added some GIFs at the end of this post for clarification.

stackoverflow - Excel VBA code to copy a specific string to clipboard
Byte Comb Copy and Paste in VBA
stackoverflow - How do I copy formatted HTML string into a Clipboard for paste using C#?
Mike Stall's .NET Debugging Blog - Copying HTML on the clipboard
The links above lead to the subroutine what puts the formated HTML link in the clipboard: 

Public Sub CopyCellHyperlinkToClipboard(sLink As String, sDescription As String)
    Dim objData As Object
    On Error Resume Next
     ' this is a late bound MSForms.DataObject
    Set objData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

     ' copy current cell formula to clipboard
    With objData
        .SetText CreateHTMLString(sLink, sDescription)
        .PutInClipboard
    End With
End Sub

stackoverflow - How to set HTML to clipboard in C#?
Mike Stall's .NET Debugging Blog - Copying HTML on the clipboard
The links above lead to the function what creates the actual HTML string: 

Function CreateHTMLString(sLink As String, sDescription As String)
    Dim sContextStart As String, sContextEnd As String, m_sDescription As String, sHtmlFragment As String, sData As String, sSourceURL As String

    m_sDescription = "Version:1.0" + Constants.vbCrLf _
                   + "StartHTML:aaaaaaaaaa" + Constants.vbCrLf _
                   + "EndHTML:bbbbbbbbbb" + Constants.vbCrLf _
                   + "StartFragment:cccccccccc" + Constants.vbCrLf _
                   + "EndFragment:dddddddddd" + Constants.vbCrLf
    sContextStart = "<HTML><BODY><!--StartFragment -->"
    'sSourceURL = "" + Constants.vbCrLf
    sHtmlFragment = "<A HREF=" + Strings.Chr(34) + sLink + Strings.Chr(34) + ">" + sDescription + "</A>"
    sContextEnd = "<!--EndFragment --></BODY></HTML>"

    sData = m_sDescription + sContextStart + sHtmlFragment + sContextEnd
    sData = Replace(sData, "aaaaaaaaaa", PadLeft(10, "0", Len(m_sDescription)))
    sData = Replace(sData, "bbbbbbbbbb", PadLeft(10, "0", Len(sData)))
    sData = Replace(sData, "cccccccccc", PadLeft(10, "0", Len(m_sDescription + sContextStart)))
    sData = Replace(sData, "dddddddddd", PadLeft(10, "0", Len(m_sDescription + sContextStart + sHtmlFragment)))
    'sData.Dump();
    CreateHTMLString = sData
End Function

Since padding isn't supported by VBA I created this function based on the the link below: stackoverflow - Any method equivalent to PadLeft/PadRight?

Function PadLeft(iLength As Integer, cChar As String, sText As String)
    cChar = Left(cChar, 1)
    PadLeft = Right(String(iLength, cChar) & sText, iLength)

End Function

Insert a hyperlink from instance of Excel 2010 to another one, as you can see it insert only the label of the hyperlink: Excel2Excel Insert a hyperlink in Excel in another application works flawless: Excel2App Therefore I wrote the code above. Now Excel2Excel works: Excel2Excel by VBA but it doesn't work for other applications: Excel2App by VBA

Stefan
  • 372
  • 1
  • 16
0

If you can create the link via VBA, it should circumvent the issue about needing to copy a link to the clipboard.

You would take your sheet you are creating links for and use a loop similar to:

With Sheets(A)
    .Hyperlinks.Add Anchor:=.Cells(1,2), Address:=.Cells(1,1), TextToDisplay:="Example" 
End With

This is a pretty general suggestion, and should allow you to look, e.g., .Cells(i,1) and .Cells(i,2) for the specific example.

Copying from one workbook to another, using a full hyperlink, rather than a cell that says =hyperlink(), will hopefully fix the issue.

Cyril
  • 6,448
  • 1
  • 18
  • 31