Range.Paste Runtime error when pasting web data

Question

Range.Paste Runtime error when pasting web data

Navigation

#1 by (1 votes)

4

Hello. I am extracting some data from an html table from an ERP which due to limitations lead me to this. Anyway, the problem consists of, when copying the table and try to paste it with range.paste or special past error.

The complete code:

Sub Coletar_Web()


    Dim ie As New SHDocVw.InternetExplorer
    Dim clip As DataObject


    With ie
        .Visible = False
        .navigate "URL"

        While .Busy Or .readyState <> 4: DoEvents: Wend

        On Error GoTo Copy

        With .document
            .getElementById("nome_u").Value = "usuario"
            .getElementById("senha").Value = "senha"
            .getElementById("submit").Click
        End With

        While .Busy Or .readyState <> 4: DoEvents: Wend
        Debug.Print .LocationURL

Copy:

        With ie
            .Visible = True
            .navigate "URL"
            Do While .Busy: DoEvents: Loop
            Do While .readyState <> 4: DoEvents: Loop
        End With

        Set ieTable = ie.document.getElementById("itens")
        If Not ieTable Is Nothing Then
            Set clip = New DataObject
            clip.SetText "<html>" & ieTable.outerHTML & "</html>"
            clip.PutInClipboard
            Sheet1.Range("A10").Paste
        End If

    End With

End Sub

The error occurs in this block:

Set ieTable = ie.document.getElementById("itens")
            If Not ieTable Is Nothing Then
                Set clip = New DataObject
                clip.SetText "<html>" & ieTable.outerHTML & "</html>"
                clip.PutInClipboard
                Sheet1.Range("A1").Paste
            End If

More specifically in Sheet1.Range ("A1"). Paste, remembering that I have used several variations of Paste and PasteSpecial to try to perform this task.

Trying to simulate this task manually and then play it back in vba, ideally using the Matching Destination formating option when casting by VBA, but I could not reproduce it efficiently. Copying and pasting simple data to arrive at this option, the ideal was PasteSpecial xlValues, but also returns error.

EDIT:

Continuing this saga, I tried to write a macro by pasting the data and got the following result:

.PasteSpecial Format:="HTML", link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True

But the error persists, probably not related to .PasteSpecial , but rather how I am storing the data.

excel vba

asked by anonymous 04.02.2016 / 20:48

1 answer

Send multiple values from my CheckBox to an Ajax call SVN Externals in Git. Exist?

score 1 · Accepted Answer

I was able to solve the problem by changing the format I was pasting. The code looks like this:

Set ieTable = IE.document.getElementById("itens")
If Not ieTable Is Nothing Then
    Set clip = New DataObject
    clip.SetText "<html>" & ieTable.outerHTML & "</html>"
    clip.PutInClipboard
    Sheet1.Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
End If