用于在特定HTML类中提取值的VBA脚本 [英] VBA script to pull values within specific HTML classes

问题描述

我创建了一个VBA脚本，通过获取HTML类中的值来从网站中提取价格。

I created a VBA script to pull prices from websites by getting the value within an HTML Class.

请参阅 VBA脚本拉来自网站的数据以获取更多背景信息。

Please see VBA Script pull data from website for more context.

这很有效但是在某些情况下只有1个价格（没有RRP和销售价格）和因此，我需要以某种方式合并一个if语句来查找一个类名，如果不存在则查找另一个。

This works really well however there are some cases where there is only 1 price (no RRP & sale price) and therefore i need to somehow incorporate an if statement to look for the a class name, if that doesn't exist look for another.

例如，我有以下电子表格：

For example I have the following spreadsheet:

| A | B | C |
| | Item | Price |
| | bfd/garden-structures/arbours/arbours-sunflower | |
| | bfd/garden-structures/arbours/tatton-corner-arbour-seat | |
| | bsd/garden-storage/wooden-storage/4-x-2-windsor-garden-storage-chest | |

在这个例子中，前两个使用下面的代码:(查看int类 VariantPrice & NowValue ）但第3个例子不能作为类 VariantPrice & NowValue 不存在，但它确实有类价格& SinglePrice 。

In this example the first 2 work with the code below: (looking int class VariantPrice & NowValue) however the 3rd example doesn't work as the classes VariantPrice & NowValue do not exist, however it does have the class price & SinglePrice.

我使用的代码如下：

Sub BuyDeckingDirect()

    Dim ie As New InternetExplorer
    Dim doc As HTMLDocument
    Dim result As IHTMLElement
    Dim result2 As IHTMLElement
    Dim item As String
    Dim lRow As Long

    'ie.Visible = True'
    lRow = 2
    item = Worksheets("BuyDeckingDirect").Range("B" & lRow).Value
    MsgBox "Price Dump Started"
    Do Until item = ""
        ie.navigate "http://www.buydeckingdirect.co.uk/" & item

        Do
            DoEvents
        Loop Until ie.readyState = READYSTATE_COMPLETE

        Set doc = ie.document

        Set result = doc.querySelector(".VariantPrice")
        Set result2 = result.querySelector(".NowValue")

        Worksheets("BuyDeckingDirect").Range("C" & lRow).Value = result2.innerText

        lRow = lRow + 1
        item = Worksheets("BuyDeckingDirect").Range("B" & lRow).Value

    Loop
    MsgBox "BuyDeckingDirect Price Dump Complete"
End Sub

任何帮助非常感谢！

谢谢

Jess

推荐答案

在对 querySelector 的调用中组合两个类，如果结果是 Nothing ，再次使用替代类调用它。

Combine both classes in the call to querySelector and if result is Nothing, call it again with the alternative class.

示例：

' ...
Set result = doc.querySelector(".VariantPrice .NowValue")
If result Is Nothing Then
    Set result = doc.querySelector(".VariantPrice .price")
End If
' You should consider the fact that you can have neither
If result Is Nothing Then
    Worksheets(...etc...).Value = "N/A"
Else
    Worksheets(...etc...).Value = result.innerText
End If

当然，您还可以在设置结果 NowValue 类c $ c>喜欢这样：

Of course you can also check for the existence of the NowValue class after setting result like so:

' ...
Set result = doc.querySelector(".VariantPrice")
If IsNull(result.querySelector(".NowValue")) Then
    Set result2 = result.querySelector(".price")
Else
    Set result2 = result.querySelector(".NowValue")
End If

就个人而言，我更喜欢第一种选择，但这取决于你的用例。

Personally, I prefer the 1st option but it's up to your use case.

这篇关于用于在特定HTML类中提取值的VBA脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！