Extracting text from a bunch of =EMBED("Forms.HTML:TextArea.1","") in Excel

I - well, my friend - has an excel spreadsheet with a mixture of regular text and textarea boxes - it has been copied from a website somewhere, she wants to just have the text in the textareas displayed rather than the boxes.

Help?

Selecting one of the textboxes / areas in "design" view shows =EMBED("Forms.HTML:TextArea.1","") in the formulae bar. They are called "Control 1", "Control 2" etc.

Is there a nice way to replace the boxes with their text? Assume some VBA-goodness, but I can't work out how to know what cell a control is in to replace itself.... wrong track entirely?

Thanks,

A开发者_如何学编程my

For Excel 2013, I had to make a minor change to get this to work. And I also want to say THANK YOU for this, it worked GREAT.

Sub Tester()
Dim o

    For Each o In ActiveSheet.OLEObjects
        If o.progID = "Forms.HTML:Text.1" Then
            Debug.Print o.Name, o.Object.Value, o.TopLeftCell.Address()
            'sometimes merged cells result from a HTML copy/paste,
            '  so don't just use .TopLeftCell to set the Value
            o.TopLeftCell.MergeArea.Value = o.Object.Value
            o.Delete
        End If
    Next o

End Sub

This might get you close.

Sub Tester()
Dim o

    For Each o In ActiveSheet.OLEObjects
        If o.progID = "Forms.HTML:TextArea.1" Then
            Debug.Print o.Name, o.Object.Value, o.TopLeftCell.Address()
            'sometimes merged cells result from a HTML copy/paste,
            '  so don't just use .TopLeftCell to set the Value
            o.TopLeftCell.MergeArea.Value = o.Object.Value
            o.Delete
        End If
    Next o

End Sub