0

I encounter error when running my Excel Macro VBA. It says: Object variable or With block variable not set

Private Sub CommandButton1_Click()
Dim ie As InternetExplorer
Dim doc As HTMLDocument
Dim rating As String

Set ie = New InternetExplorer

With ie
    .navigate "https://www.pse.com.ph/company-information-JFC/"
    .Visible = False
    Do While .readyState <> 4: DoEvents: Loop

    Set doc = ie.document

    With doc
        rating = .getElementsByClassName("last-price")(0).PreviousSibling.getElementsByTagName("h3")(0).innerText
        MsgBox rating
    End With

End With

ie.Quit

End Sub

Excel points me here:

rating = .getElementsByClassName("last-price")(0).PreviousSibling.getElementsByTagName("h3")(0).innerText

I browse through Microsoft documentations. They're telling that my variable was not set. Though at first glance, my code looks fine.

Please advise.

braX
  • 11,506
  • 5
  • 20
  • 33
SimpleGuy
  • 3
  • 2
  • Either there are no elements with class "last-price", or there are, but the first one has no `PreviousSibling`, or that sibling *does* exist, but but has no H3 child elements. It's all one line so it's difficult to know where the problem is: if you break it up it will be easier to troubleshoot. – Tim Williams Aug 11 '21 at 23:14
  • 1
    Looking at that page, it's terribly broken in IE, so maybe it's not surprising you're not finding what you're looking for. – Tim Williams Aug 11 '21 at 23:26

1 Answers1

0

The content you look for is within an iframe. You can navigate to the url within the iframe directly or start from the url in your post and then follow the url within that iframe to access the content.

Using IE (start from the url in your post):

Private Sub FetchPrice()
    Dim targetUrl$

    With CreateObject("InternetExplorer.Application")
        .Visible = False
        .navigate "https://www.pse.com.ph/company-information-JFC/"
        Do While .readyState <> 4: DoEvents: Loop
        targetUrl = .document.getElementById("company_infos").getAttribute("src")
        .navigate targetUrl
        Do While .readyState <> 4: DoEvents: Loop
        MsgBox .document.querySelector("h3.last-price").innerText
        .Quit
    End With
End Sub

If you wish to fetch the price using the content url within the iframe directly, you can go for xhr (directly using the url within the iframe):

Sub GetPrice()
    Const URL$ = "https://frames.pse.com.ph/security/jfc"
    Dim Html As HTMLDocument
    
    Set Html = New HTMLDocument

    With CreateObject("MSXML2.XMLHTTP")
        .Open "Get", URL, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.135 Safari/537.36"
        .send
        Html.body.innerHTML = .responseText
        
        MsgBox Html.querySelector("h3.last-price").innerText
    End With
End Sub

In both cases, I targeted the price from the following elements:

<div class="col-12 align-items-center px-0 font-weight-bolder">
    <h3 class="last-price">194.00</h3>
</div>
SIM
  • 21,997
  • 5
  • 37
  • 109