Curious problem copying HTML formatting from IE to Excel (but only within macro)

[dustybootz]

Hi there,
I am using MS Excel 2010 (64 bit), and IE v 9.0.8112.16421 (64-bit), Windows 7 Professional

I am trying to use an Excel macro to copy data from a webpage. To do this I am automating IE in a fairly standard manner as follows:

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.racingpost.com/horses/result_home.sd?race_id=337629" 
Do Until .ReadyState = 4: DoEvents: Loop
IE.ExecWB 17, 0 ' Select All
IE.ExecWB 12, 2 ' Copy selection

ActiveSheet.PasteSpecial Format:="HTML", link:=False, NoHTMLFormatting:=True
End With

The problem with the above is this: I need to copy/paste using the HTML structure as this creates a nice, neat table in Excel that is much easier for me to process- but WITHOUT the HTML formatting. When I do this manually (i.e., when I select the IE window and select all / copy and then go to the excel worksheet and right-click over a cell to view the paste options) I am given two such options (indicated by icons): keeping source formatting (K) or matching source formatting (M). However when I tried this from within a macro I get an error (run-time error '1004' PasteSpecial method of Worksheet Class Failed). This is understandable as when I step-through the macro and attempt to paste the clipboard manually into Excel (i.e., after the line "IE.ExecWB 12, 2") I do NOT see the two paste options listed above (K or M)- only an option to paste text. For some reason, the automated select all and copy in IE does not seem to preserve the HTML elements in the same way as doing this manually. Also- and here's the curious bit- if I step-through the macro and direct the focus onto IE (by clicking WITHIN the window- not the frame around it) before the ExecWB statements then the paste special command DOES work (no error).

This indicates the error is caused (originally) by what is happening in IE (the copying part), so my attempts to solve this have been directed around producing an automated way to replicate a mouse click within the IE window- I have been doing this for a whole day now by reading posts on forums and have failed and am generally going mad and falling behind with my project! Hence I have written this post. It is an extremely frustrating problem as this one little hiccup is preventing a whole show from running. Anyone pointing me towards a working solution would be a saint in my eyes(!)

Thanks for reading the post

 

[dustybootz]

Any thoughts on this issue (or even a different place I could search for information)?
Best regards,
David