Extract links from web site using VBA

viktor4e

New Member
Joined
Jun 30, 2014
Messages
16
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try this:

Code:
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top