"Paste method of worksheet class failed." Copying from webpage

**bubleeshaark** · 06-26-2013, 12:26 PM

Excel 2007

I know this error has been solved many times before, but I have only seen workarounds, and my script requires an actual solution.

I am running a VBA script in excel that copies (10-600) lines of information from a webpage and then pastes that into excel. The pulled information contains headers and tables, and excel nicely keeps their format when I paste.

The paste works fine except for a random "runtime error 1004 paste method of worksheet class failed." As other posters have noted, the error goes away if you just hit 'Debug' and press play again. I could live with that myself, but this script will be used by others.

The solutions on forums say to assign the cells the string instead of copy-paste into it. That's a great solution for them, but doesn't work for me because I need to keep the formatting from an external source (webpage). So the following does not work, it just puts everything into one column:

Range("A1","B2").Value = ie.document.body.innerhtml

So here's my code:

Do
'stuff
            clipboard.Clear
            Set clipboard = New MSForms.DataObject
            clipboard.SetText ie.document.body.innerhtml ' <-- if I use innerTEXT it doesn't copy the tables because the source code has 2 <body> tags. GRR!!
            clipboard.PutInClipboard
            Cells(FR, 1).Select
            ClearTextToColumns ' <-- This is a little macro that prevents TTC from messing with your paste
            clipboard.GetFromClipboard
            ActiveSheet.Paste
            clipboard.Clear
'more stuff
Loop

**patel45** · 06-26-2013, 01:17 PM

Do
'stuff
            clipboard.Clear
            Set clipboard = New MSForms.DataObject
            clipboard.SetText ie.document.body.innerhtml ' <-- if I use innerTEXT it doesn't copy the tables because the source code has 2 <body> tags. GRR!!
            clipboard.PutInClipboard
            Cells(FR, 1).Select
            ClearTextToColumns ' <-- This is a little macro that prevents TTC from messing with your paste
            clipboard.GetFromClipboard
            Cells(FR, 1).pastespecial
            clipboard.Clear
'more stuff
Loop