如何阅读html代码以便用excel vba获取数据 [英] How to read html code in order to grab data with excel vba
问题描述
HTML代码是：
< div id =PriceLabelclass =skuPrice formRow>
< span class =skuPriceWrp>
< strong class =price redid =skuPriceLabelstyle =font-size：20px;>
< span class =VAT> eks mva< / span>< br>
< span itemprop =priceid =SkuPriceUpdate> kr
< span itemprop =priceCurrencycontent =NOK> 151,20< / span>
< / span>
< / strong>
< span> /
< span class =UOMtelephone =no> RL
< / span>
< / span>
< / span>
< span class =skuUOMWrp>
< span id =showEachPriceclass =clear>< / span>
< / span>
< div class =formRow clearid =divSkuSavePrice>
< span id =divSkuWhenYouBuy>
< span class =whenYouSave>< / span>& nbsp;
< / span>
< / div>
< / div>
我想要获取的值是 151,20 ，位于第一个跨度上。
我试过了： .document.getElementById skuPriceLabel）。getElementsByTagName（span）（0）.innerText
这有时会给出值Eks mva我试过使用 .document.getElementById（skuPriceLabel）。getElementsByTagName（span ）（1）.innerText
和
.document.getElementById（skuPriceLabel）。getElementsByTagName（span）（2）.innerText
以及那些给出424运行时错误。
有人可以告诉我如何阅读html代码我可以看到逻辑并知道下一次要查找和写什么？
我的完整代码是
Sub get_data_2（）
'此代码的源代码是：
'http://stackoverflow.com/questions/26613043/get-data- out-of-a-page-with-vba
Dim ie As Object
Dim sht As Worksheet
Dim SKU As String
Dim RowCount As Long
Set sht = Sheet8
Set ie = CreateObject（InternetExplorer.application）
RowCount = 1
' 1.
sht.Range（a& RowCount）=SKU'A列中填入了SKU的名称。
sht.Range（n& RowCount）=价格'N列将给出SKU的价格。
用ie
.Visible = True
.navigatehttp://www.staples.no/
Do While .Busy或_
.readyState<> 4
DoEvents
Loop
RowCount = RowCount + 1
SKU = sht.Range（a& RowCount）.Value
用ie'填入搜索框并提交。
ie.document.all（searchKeywords）。Value = SKU'我们可以在这个例子中使用491215作为SKU。
ie.document.forms（searchForm）。submit
Do While .Busy或_
.readyState<> 4
DoEvents
Loop
'将价格写入列N
sht.Range（n& RowCount）.Value = ie.document.getElementById（ （span）（1）.getElementsByTagName（span）（0）.innerText
End With
Loop While sht.Range（a& amp; ; RowCount + 1）.Value<> '只要A列有一个SKU（直到列表结束），循环。
End With
Set ie = Nothing
End Sub
我会说你有两个选择：
1。 DOM
.document.getElementById（skuPriceLabel）。getElementsByTagName（span）（1）.getEl ementsByTagName（span） （0）.innerText
2。正则表达式
使用正则表达式： content =NOK>（。*？）< 函数
Public Function GetRegex（str As String，reg As String，可选索引As Integer）As String
On Error Resume Next
Set regex = CreateObject（VBScript.RegExp）
regex.Pattern = reg
regex.Global = True
如果index< 0然后index = 0
如果regex.test（str）那么
设置matches = regex.Execute（str）
GetRegex = matches（index）.SubMatches（0）
Exit Function
End If
GetRegex =
End Function
I'm trying to grab som data from a webpage with Excel VBA.
The HTML code is:
<div id="PriceLabel" class="skuPrice formRow">
<span class="skuPriceWrp">
<strong class="price red" id="skuPriceLabel" style="font-size: 20px;">
<span class="VAT">eks mva </span><br>
<span itemprop="price" id="SkuPriceUpdate">kr
<span itemprop="priceCurrency" content="NOK">151,20</span>
</span>
</strong>
<span> /
<span class="UOM" telephone="no">RL
</span>
</span>
</span>
<span class="skuUOMWrp">
<span id="showEachPrice" class="clear"></span>
</span>
<div class="formRow clear" id="divSkuSavePrice">
<span id="divSkuWhenYouBuy">
<span class="whenYouSave"></span>
</span>
</div>
</div>
What i want to grab is the value 151,20, located on the first "span".
I've tried: .document.getElementById("skuPriceLabel").getElementsByTagName("span")(0).innerText
This gives the value "Eks mva" sometimes, and runtime error 424 (object required) at other times.
I've tried using .document.getElementById("skuPriceLabel").getElementsByTagName("span")(1).innerText
and
.document.getElementById("skuPriceLabel").getElementsByTagName("span")(2).innerText
as well, but those give the 424 runtime error.
Can somebody please give show me how to read the html code so i can see the logic and know what to look for and write the next time?
My full code is
Sub get_data_2()
'Source for this code is:
'http://stackoverflow.com/questions/26613043/get-data-out-of-a-webpage-with-vba
Dim ie As Object
Dim sht As Worksheet
Dim SKU As String
Dim RowCount As Long
Set sht = Sheet8
Set ie = CreateObject("InternetExplorer.application")
RowCount = 1
'This just gives the columns a titel i row numer 1.
sht.Range("a" & RowCount) = "SKU" 'Column A is populated with SKU's to be looked up.
sht.Range("n" & RowCount) = "Price" 'Column N will be given the price of the SKU.
With ie
.Visible = True
.navigate "http://www.staples.no/"
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
Do
RowCount = RowCount + 1
SKU = sht.Range("a" & RowCount).Value
With ie 'fill in the searchbox and submit.
ie.document.all("searchKeywords").Value = SKU 'we can use 491215 as a SKU for this example.
ie.document.forms("searchForm").submit
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
'write the price to column N
sht.Range("n" & RowCount).Value = ie.document.getElementById("skuPriceLabel").getElementsByTagName("span")(1).getElementsByTagName("span")(0).innerText
End With
Loop While sht.Range("a" & RowCount + 1).Value <> "" 'Loop as long as column A has a SKU (till end of list).
End With
Set ie = Nothing
End Sub
I would say you have 2 options:
1. DOM
.document.getElementById("skuPriceLabel").getElementsByTagName("span")(1).getElementsByTagName("span")(0).innerText
2. Regex
Use regex: content=""NOK"">(.*?)< with this function
Public Function GetRegex(str As String, reg As String, Optional index As Integer) As String
On Error Resume Next
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = reg
regex.Global = True
If index < 0 Then index = 0
If regex.test(str) Then
Set matches = regex.Execute(str)
GetRegex = matches(index).SubMatches(0)
Exit Function
End If
GetRegex = ""
End Function
这篇关于如何阅读html代码以便用excel vba获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
