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

Solution

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.