“粘贴"excel VBA中的字符串变量，而不是剪贴板中的内容? [英] "Paste" string variable in excel VBA instead of the contents of clipboard?

问题描述

我有一个字符串变量，其中包含excel VBA中的HTML表.我知道，当将此表存储在剪贴板中并调用.PasteSpecial时，Excel会进行一些漂亮的预处理，并以与表中显示单元相同的方式填充当前工作表中的单元格.

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.

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

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.

那么，在为带格式字符串的范围设置值时，是否有任何方法可以使粘贴预处理"发生?

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

推荐答案

这只是一条评论(stackeoverflow尚未允许我以正确的方式发表评论).

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

您可能可以使用某些API来实现此目的.

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

很久以前，我玩过它(寻找一种欺骗MS Word的方法)，我记得只要输入内容类型的正确ID(例如纯文本)，您就可以将任何内容存储到剪贴板.，格式化的文本， html 等).存储内容后，必须使用相应的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.

我的进度没有达到我的预期，而且时间紧迫，所以我放弃了这个主意.如果您想给它一个机会，请在MSDN中查找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).

我找到了代码.以下所有代码应保存在模块中:

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

这篇关于“粘贴"excel VBA中的字符串变量，而不是剪贴板中的内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！