Extract links from web site using VBA

[viktor4e]

Hi everyone,

The following web site lists 63 products (fragrances):
Dolce&Gabbana Perfumes And Colognes

Using VBA, I would like to extract all the links to each of the 63 products in an excel file. An example of an outcome would be:
/perfume/Dolce-Gabbana/Dolce-Gabbana-Pour-Homme-15336.html

Ideally I would like to specify the web site in a cell and the macro to go to the "view-source" page and extract all the data. The code should work in any given scenario as some brands have more than 200 products listed.

Thank you in advance.

 

[Ombir]

Try this:

Sub Ombir_21Dec2016()
Dim r           As Long
Dim ie          As InternetExplorer
Dim doc         As HTMLDocument
Dim perfumes    As IHTMLElementCollection
Dim perfume     As HTMLDivElement
Dim output      As Variant

Set ie = New InternetExplorer

With ie
    .Visible = True
    .Navigate "http://www.fragrantica.com/designers/Dolce%26Gabbana.html"
    Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
End With

Set doc = ie.Document
Set perfumes = doc.getElementsByClassName("perfumeslist")

ReDim output(1 To perfumes.Length, 1 To 2)

For Each perfume In perfumes
    r = r + 1
    output(r, 2) = perfume.getElementsByTagName("a")(0).href
    output(r, 1) = LTrim(perfume.getElementsByTagName("a")(0).innerText)
Next
ie.Quit
Range("A1:B1") = Array("Product Name", "Link")
Range("A2").Resize(UBound(output), 2) = output
ActiveSheet.UsedRange.Columns.AutoFit
ActiveSheet.UsedRange.Borders.Weight = 2
ActiveSheet.Range("A1:B1").Interior.ThemeColor = xlThemeColorAccent1
End Sub

 

[viktor4e]

This is impressive! Thank you very much!

How can this code be tweaked so that I can provide a list of links (separate brands, so it could be a lot of them) in a column and the macro to loop through each link and provide the output in the same sheet?

 

[Macropod]

Duplicate thread: http://www.mrexcel.com/forum/excel-...web-site-using-visual-basic-applications.html
Please read our policy against starting duplicate threads in rule #12: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[jayymehta]

Hi, sorry for awaking such old thread, but just wanted to know that, besides name and URL, if we also want to add the price of each item, shall we have to form a separate loop for it? I think that the className "perfumeslist" does not contain price, "mtext" className does. If so, what does this "span" refer to, written just before "class" in HTML tags?