Get data from website

[bhinangt]

i want ONLY buy and sell price in excel from https://bx.in.th/
Is there any <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code that can help?

Buy and sale price are first price from buy and sale books

I tried Data>Import from Web menu but that gets full web site content which takes too long time to load...

Thanks in advance

 

[Domenic]

First set a reference to the following library...

Tools > Reference > Microsoft WinHTTP Services, version 5.1 (or the latest version that you have)

Then try...

[COLOR=darkblue]Sub[/COLOR] GetFirstBuyAndSellPrice()

    [COLOR=darkblue]Dim[/COLOR] oWinHTTP [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] WinHttp.WinHttpRequest
    [COLOR=darkblue]Dim[/COLOR] oHTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] oFirstBuyRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] oFirstSellRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    
    [COLOR=darkblue]With[/COLOR] oWinHTTP
        .Open "GET", "https://bx.in.th/", [COLOR=darkblue]False[/COLOR]
        .send
        [COLOR=darkblue]If[/COLOR] .Status <> 200 [COLOR=darkblue]Then[/COLOR]
            MsgBox "Error " & .Status & ":  " & .statusText
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        oHTMLDoc.body.innerHTML = .responseText
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] oFirstBuyRow = oHTMLDoc.getElementById("highbid").getElementsByTagName("tr")(1)
    [COLOR=darkblue]Set[/COLOR] oFirstSellRow = oHTMLDoc.getElementById("lowask").getElementsByTagName("tr")(1)
    
    [COLOR=green]'Print to the Immediate Window the first buy price[/COLOR]
    Debug.Print oFirstBuyRow.Cells(0).innerText, oFirstBuyRow.Cells(1).innerText, oFirstBuyRow.Cells(2).innerText
    
    [COLOR=green]'Print to the Immediate Window the first sell price[/COLOR]
    Debug.Print oFirstSellRow.Cells(0).innerText, oFirstSellRow.Cells(1).innerText, oFirstSellRow.Cells(2).innerText
    
    [COLOR=darkblue]Set[/COLOR] oWinHTTP = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] oHTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[bhinangt]

Thanks for the God Level Trick!

First set a reference to the following library...

Tools > Reference > Microsoft WinHTTP Services, version 5.1 (or the latest version that you have)

Then try...

[COLOR=darkblue]Sub[/COLOR] GetFirstBuyAndSellPrice()

    [COLOR=darkblue]Dim[/COLOR] oWinHTTP [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] WinHttp.WinHttpRequest
    [COLOR=darkblue]Dim[/COLOR] oHTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] oFirstBuyRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] oFirstSellRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    
    [COLOR=darkblue]With[/COLOR] oWinHTTP
        .Open "GET", "https://bx.in.th/", [COLOR=darkblue]False[/COLOR]
        .send
        [COLOR=darkblue]If[/COLOR] .Status <> 200 [COLOR=darkblue]Then[/COLOR]
            MsgBox "Error " & .Status & ":  " & .statusText
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        oHTMLDoc.body.innerHTML = .responseText
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] oFirstBuyRow = oHTMLDoc.getElementById("highbid").getElementsByTagName("tr")(1)
    [COLOR=darkblue]Set[/COLOR] oFirstSellRow = oHTMLDoc.getElementById("lowask").getElementsByTagName("tr")(1)
    
    [COLOR=green]'Print to the Immediate Window the first buy price[/COLOR]
    Debug.Print oFirstBuyRow.Cells(0).innerText, oFirstBuyRow.Cells(1).innerText, oFirstBuyRow.Cells(2).innerText
    
    [COLOR=green]'Print to the Immediate Window the first sell price[/COLOR]
    Debug.Print oFirstSellRow.Cells(0).innerText, oFirstSellRow.Cells(1).innerText, oFirstSellRow.Cells(2).innerText
    
    [COLOR=darkblue]Set[/COLOR] oWinHTTP = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] oHTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[bhinangt]

I tried it to learn from your way but unfortunately its blank for me... Whats the error in my code??

Sub Zebpay()


Dim oWinHTTP As New WinHttp.WinHttpRequest
Dim oHTMLDoc As New MSHTML.HTMLDocument
Dim sbuy As String

With oWinHTTP
.Open "GET", "https://www.zebpay.com/", False
.send
If .Status <> 200 Then
MsgBox "Error " & .Status & ": " & .statusText
Exit Sub
End If
oHTMLDoc.body.innerHTML = .responseText
End With

MsgBox oHTMLDoc.getElementById("buy").innerText, vbOKOnly


Set oWinHTTP = Nothing
Set oHTMLDoc = Nothing

End Sub

 

[Domenic]

It looks like that part gets loaded after the main page is loaded, so it won't be part of the WinHTTP content. So I think you'll need to use Internet Explorer instead. Note that you can set the Visible property to False so that you won't see Internet Explorer when the code runs.

[COLOR=darkblue]Sub[/COLOR] Zebpay()

    [COLOR=green]'Set a reference (VBE > Tools > References) to the following libraries:[/COLOR]
    [COLOR=green]'   1) Microsoft Internet Controls[/COLOR]
    [COLOR=green]'   2) Microsoft HTML Object Library[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] IE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] SHDocVw.InternetExplorer
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    
    [COLOR=darkblue]With[/COLOR] IE
        .Visible = [COLOR=darkblue]True[/COLOR]
        .navigate "https://www.zebpay.com/"
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .Busy [COLOR=darkblue]Or[/COLOR] .readyState <> READYSTATE_COMPLETE
            DoEvents
        [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = IE.document
    
    Debug.Print HTMLDoc.getElementById("buy").innerText
    
    IE.Quit
    
    [COLOR=darkblue]Set[/COLOR] IE = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!