Use VBA to open Chrome and copy text to a cell

[kyk8898]

Hi there,

I would like to use excel VBA to open the Chrome browser and a specific link and then copy all the text on that page to a cell in a worksheet.

The page I want to copy is as below:
http://bet.hkjc.com/racing/getJSON.aspx?type=win&date=2018-09-02&venue=ST&raceno=1

I found the code below that can open the browser but I don't know how to extract the text and send it to the cell (say A1 in Sheet 3)

==================================================================
Sub test544()

Dim chromePath As String

chromePath = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""


Shell (chromePath & "http://bet.hkjc.com/racing/getJSON.aspx?type=qpl&date=2018-09-02&venue=ST&raceno=1")

End Sub
==================================================================

Can anyone help? Thanks
KK

 

[Logit]

.
Try this macro :

Option Explicit


Sub Test()


    Dim IE As Object
    On Error Resume Next
    Application.DisplayAlerts = False
    
    Sheets("Sheet3").Select
    Range("A1:A1000") = "" ' erase previous data
    Range("A1").Select


    Set IE = CreateObject("InternetExplorer.Application")
        With IE
            .Visible = True
            .Navigate "https://stackoverflow.com/questions/26561527/vba-to-copy-website-data" ' should work for any URL
            Do Until .ReadyState = 4: DoEvents: Loop
        End With


        IE.ExecWB 17, 0 '// SelectAll
        IE.ExecWB 12, 2 '// Copy selection
        ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
        Range("A1").Select
        IE.Quit


    Application.DisplayAlerts = True
End Sub

Resource : https://stackoverflow.com/questions/26561527/vba-to-copy-website-data

The url link you provided had on one word ....

 

[kyk8898]

It works. Thank you so much.

 

[Logit]

.
You are welcome.

 

[kyk8898]

Hi Logit

The code suddenly does not work today. It seems that it fails to invoke IE and nothing was copied. Please help. Thanks. KK

Sub Test()


Dim IE As Object
On Error Resume Next
Application.DisplayAlerts = False

Sheets("Sheet3").Select
Range("A1:B2") = "" ' erase previous data
Range("A1").Select


Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.Navigate "https://bet.hkjc.com/racing/getJSON.aspx?type=win&date=2018-12-12&venue=HV&raceno=1" ' should work for any URL
Do Until .ReadyState = 4: DoEvents: Loop
End With


IE.ExecWB 17, 0 '// SelectAll
IE.ExecWB 12, 2 '// Copy selection
ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
Range("A1").Select
IE.Quit


Application.DisplayAlerts = True
End Sub

 

[kyk8898]

Oh... one more thing, the macro stopped at the following line:

Do Until .ReadyState = 4: DoEvents: Loop