excel vba：当从另一个excel实例复制时，具有超链接公式的应对单元格失败

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.

当我复制一个包含超链接公式的单元格时，它将作为由公式格式化的html链接插入。只有接收应用程序不是另一个Excel 2010实例/应用程序对象时，它才有效。当我将它从原始实例复制到另一个实例时，它只复制纯文本。

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

我可以编写什么把html格式的文本放在剪贴板而不是Excel单元格内容对象？

2 个解决方案

#1

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

有一个解决Excel到Excel复制问题的工作，但现在我在任何其他应用程序中粘贴它时遇到问题

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.

根据以下帖子，我调整了VBA所提到的代码。现在的问题是，Windows现在将纯文本粘贴到其他应用程序中，但它在Excel中完美无缺。我在本文末尾添加了一些GIF以便澄清。

* - Excel VBA code to copy a specific string to clipboard
Byte Comb Copy and Paste in VBA
* - 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:

* - 用于将特定字符串复制到剪贴板的Excel VBA代码Byte Comb复制并粘贴到VBA *中 - 如何将格式化的HTML字符串复制到剪贴板中以使用C＃进行粘贴？ Mike Stall的.NET调试博客 - 在剪贴板上复制HTML上面的链接指向子程序，它将格式化的HTML链接放在剪贴板中：

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

* - 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:

* - 如何在C＃中将HTML设置为剪贴板？ Mike Stall的.NET调试博客 - 在剪贴板上复制HTML上面的链接指向创建实际HTML字符串的函数：

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: * - Any method equivalent to PadLeft/PadRight?

由于VBA不支持填充，我基于以下链接创建了此函数：* - 任何等效于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: Insert a hyperlink in Excel in another application works flawless: Therefore I wrote the code above. Now Excel2Excel works: but it doesn't work for other applications:

从Excel 2010的实例插入一个超链接到另一个超链接，因为你可以看到它只插入超链接的标签：在Excel中插入一个超链接在另一个应用程序中完美无缺：因此我编写了上面的代码。现在Excel2Excel可以工作：但它不适用于其他应用程序：

#2

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

如果您可以通过VBA创建链接，它应该避免需要将链接复制到剪贴板的问题。

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.

这是一个相当普遍的建议，并且应该允许您查看例如.Cells（i，1）和.Cells（i，2）以获取特定示例。

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

使用完整的超链接而不是使用= hyperlink（）的单元格从一个工作簿复制到另一个工作簿，将有望解决问题。

#1