Range.Paste Runtime error when pasting web data

Asked

Viewed 233 times

4

Hello. I am extracting some data from an html table of an ERP that due to limitations lead me to it. Anyway, the problem consists of, when copying the table and trying to glue it with range.Paste or pastespecial 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’ve used several variations of Paste and Pastspecial to try this task.

Trying to simulate this task manually to then play it in vba, the ideal would be using the Matching Destination formating option when pasting by VBA, but also could not play it efficiently. Copying and pasting simple data to get to this option, the ideal was Pastespecial xlValues, but also returns error.

EDIT:

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

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

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

excel vba

1 answer

1


I was able to solve the problem by changing the format I was pasting. The code looked 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

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.