Trouble with copy-pasting a web page through Excel VBA

Question

I'm having trouble selecting the entire webpage, copying it, and pasting onto the excel spreadsheet using the code below through VBA. Also, I'm sure I haven't properly put instructions for the copy-paste procedure.

References for VBA:

Sub Horse10()

Dim ws As Worksheet
Dim http As New XMLHTTP60
Dim html As New HTMLDocument

 
  Set ws = ThisWorkbook.Worksheets("Sheet1")
          
    With http
        .Open "GET", "https://www.racingandsports.com.au/Jockey/GetJockeyStats?jockeyIdStr=18658&discipline=T&season=All%20Seasons&hemisphere=S&_=1629813279071", False
        .send

        '''The issue starts here

        http.SelectAll.Copy
        ws.Cells(2, 10).Select.Paste
    End With
 
End Sub

can't quite remember but feel like you have to wait for the response to come back from the send — InjuredCoding, Jul 01 '22 at 11:37

score 0 · Answer 1 · answered Jul 01 '22 at 12:18

I used the clipboard function from another answer to get the data to the clipboard, then ws.Paste to paste it in the worksheet:

Option Explicit

Sub Horse10()
    Dim ws As Worksheet
    Dim http As New XMLHTTP60
    'Dim html As New HTMLDocument

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With http
        .Open "GET", "https://www.racingandsports.com.au/Jockey/GetJockeyStats?jockeyIdStr=18658&discipline=T&season=All%20Seasons&hemisphere=S&_=1629813279071", False
        .send

        '''The issue starts here
        Call Clipboard(http.responseText)

        ws.Paste Destination:=ws.Cells(2, 10)
    End With
End Sub

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
        With .parentWindow.clipboardData
            Select Case True
                Case Len(s): .setData "text", v
                Case Else:   Clipboard = .GetData("text")
            End Select
        End With
    End With
End Function

This solution works perfectly, although it includes hyperlinks. — NewGuy1, Jul 01 '22 at 13:28
You could use `PasteSpecial` with the `NoHtmlFormatting` parameter set to `True` to get rid of all formatting and links. See https://learn.microsoft.com/sv-se/office/vba/api/excel.worksheet.pastespecial for more. — Olle Sjögren, Jul 02 '22 at 14:11

Trouble with copy-pasting a web page through Excel VBA

1 Answers1