vba excel VBA中的“粘贴”字符串变量而不是剪贴板的内容？

"Paste" string variable in excel VBA instead of the contents of clipboard?

提问by QTPprogrammer

I have a string variable that contains an HTML table in excel VBA. I know that when this table is stored in the clipboard and I invoke .PasteSpecial, Excel does some nifty preprocessing and fills the cells out in the current sheet the same way as they appear in the table.

我有一个字符串变量，其中包含 Excel VBA 中的 HTML 表。我知道当这个表存储在剪贴板中并且我调用 .PasteSpecial 时，Excel 会进行一些漂亮的预处理，并按照它们在表中出现的相同方式填充当前工作表中的单元格。

However, if I simply set the .Value of a cell/range to the string variable, no such preprocessing takes place and the entire string, HTML tags and all, are dumped into the cell. I want the former result, but I cannot use the clipboard because it is being used by this application elsewhere and there is no guarantee I would not overwrite critical data. It is also being used asynchronously so I cannot simply save the current contents of the clipboard, use the clipboard, and then restore the previous contents of the clipboard.

但是，如果我只是将单元格/范围的 .Value 设置为字符串变量，则不会进行此类预处理，并且整个字符串、HTML 标记和所有内容都将转储到单元格中。我想要前一个结果，但我不能使用剪贴板，因为它被这个应用程序在其他地方使用，并且不能保证我不会覆盖关键数据。它也被异步使用，所以我不能简单地保存剪贴板的当前内容，使用剪贴板，然后恢复剪贴板的先前内容。

So, is there any way to get the "pasting preprocessing" to occur when setting the value for a range with a formatted string?

那么，在使用格式化字符串设置范围的值时，有什么方法可以使“粘贴预处理”发生？

回答by QTPprogrammer

I would still be curious to know the answer if anyone has it, but I decided to just go ahead and abandon the idea of storing the table in a worksheet. Instead I parse the table myself and find the values I need using the InStr function (as they are mostly adjacent key=value pairs), which is not terribly slow for my application.

如果有人有答案，我仍然很想知道答案，但我决定继续并放弃将表格存储在工作表中的想法。相反，我自己解析表并使用 InStr 函数找到我需要的值（因为它们大多是相邻的键=值对），这对我的应用程序来说并不是很慢。

回答by Dick Kusleika

I can't think of anyway to invoke Excel's preprocessor without the clipboard. For parsing, you may want to check out the Split function. Here's an example.

我想不出在没有剪贴板的情况下调用 Excel 的预处理器。对于解析，您可能需要查看 Split 函数。这是一个例子。

Sub ParseTable()

    Dim sHtmlTable As String
    Dim vaTable As Variant
    Dim i As Long

    Const STDSTART = "<td"
    Const STDEND = "</td"

    sHtmlTable = "<table border=""1""><tr><td>row 1, cell 1</td><td>row 1, cell 2</td></tr><tr><td>row 2, cell 1</td><td>row 2, cell 2</td></tr></table>"

    vaTable = Split(sHtmlTable, ">")

    For i = LBound(vaTable) To UBound(vaTable)
        If vaTable(i) = STDSTART Then
            Debug.Print Replace(vaTable(i + 1), STDEND, "")
        End If
    Next i

End Sub

回答by Oneide

This is just a comment (stackeoverflow doesn't let me comment the propper way yet).

这只是一个评论（stackeoverflow 还没有让我以正确的方式评论）。

You probably could do it the way you want using some API.

您可能可以使用某些 API 以您想要的方式进行操作。

A long time ago I played with it (looking for some way to cheat MS Word) and I remember that you could store any content to the clipboard, as long as you enter the right id of the content type (like pure text, formated text, html, etc). After storing the content, you must use the respective API function to paste, again, the right type of content.

很久以前我玩过它（寻找某种欺骗 MS Word 的方法），我记得你可以将任何内容存储到剪贴板，只要你输入内容类型的正确 id（如纯文本、格式化文本） 、html等）。存储内容后，您必须使用相应的 API 函数再次粘贴正确类型的内容。

I didn't make progress as fast as I expected, and I was short of time, so I abandoned the idea. If you would like to give it a chance, look up MSDN for the API calls (I don't have it here right now, otherwise I would give you right away).

我的进度没有想象的那么快，时间也不够用，所以放弃了这个想法。如果您想给它一个机会，请在 MSDN 上查找 API 调用（我现在这里没有，否则我会立即提供给您）。

EDIT: I found the code. All the code below should be kept in a module:

编辑：我找到了代码。下面的所有代码都应该保存在一个模块中：

' Clipboard functions:
Private Declare Function OpenClipboard Lib "USER32" (ByVal hWnd As Long) As Long
Private Declare Function CloseClipboard Lib "USER32" () As Long
Private Declare Function GetClipboardData Lib "USER32" (ByVal wFormat As Long) As Long
Private Declare Function IsClipboardFormatAvailable Lib "USER32" (ByVal wFormat As Long) As Long
Private Declare Function RegisterClipboardFormat Lib "USER32" Alias "RegisterClipboardFormatA" (ByVal lpString As String) As Long
' Memory functions:
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)

Public Function GetClipboardIDForCustomFormat(ByVal sName As String) As Long
Dim wFormat As Long
    wFormat = RegisterClipboardFormat(sName & Chr$(0))
    If (wFormat > &HC000&) Then
        GetClipboardIDForCustomFormat = wFormat
    End If
End Function

Public Function GetClipboardDataAsString(ByVal lFormatID As Long) As String
'Public Function GetClipboardDataAsString(ByVal hWndOwner As Long, ByVal lFormatID As Long) As String
Dim bData() As Byte
Dim hMem As Long
Dim lSize As Long
Dim lPtr As Long

    ' Open the clipboard for access:
    If (OpenClipboard(0&)) Then
'    If (OpenClipboard(hWndOwner)) Then
        ' Check if this data format is available:
        If (IsClipboardFormatAvailable(lFormatID) <> 0) Then
            ' Get the memory handle to the data:
            hMem = GetClipboardData(lFormatID)
            If (hMem <> 0) Then
                ' Get the size of this memory block:
                lSize = GlobalSize(hMem)
                If (lSize > 0) Then
                    ' Get a pointer to the memory:
                    lPtr = GlobalLock(hMem)
                    If (lPtr <> 0) Then
                        ' Resize the byte array to hold the data:
                        ReDim bData(0 To lSize - 1) As Byte
                        ' Copy from the pointer into the array:
                        CopyMemory bData(0), ByVal lPtr, lSize
                        ' Unlock the memory block:
                        GlobalUnlock hMem

                        ' Now return the data as a string:
                        GetClipboardDataAsString = StrConv(bData, vbUnicode)

                    End If
                End If
            End If
        End If
        CloseClipboard
    End If
End Function