CODEWITHSUNDEEP
exceluserformvba
rheitzman
rheitzman

Reputation: 2297

Excel Userform - get HTML from clipboard

Excel 2010, 2013

I have some HTML on the clipboard and I want to parse it via an Excel UserForm.

I can retrieve the formats on the clipboard using VB.Net and "HTML Format" is listed in the returned array. Howerver s = MyDataobj.GetText("HTML Format") in EXCEL VBA fails. In fact I can't get any param passed to GetText() to return anything. I can paste the clipboard to a spreadsheet and Excel pastes the HTMl table just fine.

The program that placed the data on the Clipboard was Lotus Notes so who know what exotic formats might be present.

Is there a way I can discover in VBA the available formats (and the magic numbers/strings to retrieve the data) that are available from the DataObject?

Here is the code I have for extracting text. I should be able to retrieve other formats via GetText but I don't know the parameter values to pass.

        Public Function GetText() As String
        On Error GoTo Local_err
            Dim MyData   As DataObject
            Dim strClip   As String

            Set MyData = New DataObject
            MyData.GetFromClipboard
            GetText = MyData.GetText
        local_exit:
            Exit Function
        Local_err:
            MsgBox Err & " " & Err.Description & vbCrLf & vbCrLf & "GetText from Clipboard: text not found"
            Resume local_exit
            Resume
        End Function

Upvotes: 3

Views: 2843

Answers (2)

Traveler
Traveler

Reputation: 255

You can access alternate formats from the clipboard in VBA.

This "recipe" (found at vbaccelerator) will do the trick:

1) Create a new code module and place the following code into it:

' 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 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(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

2) In your code insert something like

Dim myContent As String 
myContent = GetClipboardDataAsString(0, 49382)

where 49382 is the Format ID for HTML.

You may use NirSofts Freeware InsideClipboard to display the possibly multiple contents of your current clipboard and the associated Format IDs.

Upvotes: 0

JosieP
JosieP

Reputation: 3410

With VBA all you can get is text from the dataobject. I reckon you'll need api calls for this-Chip Pearson has sample code: http://www.cpearson.com/excel/Clipboard.aspx which may assist you.

Upvotes: 3

Related Questions