Excel VBA错误:未设置对象变量或With块变量

Question

我在运行Excel宏VBA时遇到错误。它说:对象变量或块变量未设置

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告诉我这里:

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

我浏览微软文档。它告诉我变量没有设置。虽然乍一看，我的代码看起来很好。

请建议。

Answer 1

您查找的内容在iframe内。你可以直接导航到iframe内的url，或者从你的文章中的url开始，然后按照该iframe内的url访问内容。

使用IE(从你帖子中的url开始):

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

如果您希望直接使用iframe内的内容url获取价格，您可以使用xhr(直接使用iframe内的url):

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

在这两种情况下，我的目标价格来自以下元素:

<div class="col-12 align-items-center px-0 font-weight-bolder">
<h3 class="last-price">194.00</h3>
</div>