CODEWITHSUNDEEP
htmlvbaexcelif-statement
Jess Murray
Jess Murray

Reputation: 1339

VBA script to pull values within specific HTML classes

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

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

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 | |

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.

The code I have used is below:

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

Any help would be really appreciated!

Thanks

Jess

Upvotes: 1

Views: 739

Answers (1)

ssarabando
ssarabando

Reputation: 3517

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

Example:

' ...
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

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.

Upvotes: 1

Related Questions