u/HezzyBear_97

I had to share it with you all. Every week I'd manually open a supplier's pricing page, copy the table, paste it into Excel, fix the formatting that always broke, delete the garbage rows, and repeat for 24 different sites. Easily around 2 hours every Friday. I just assumed that's how it worked. Nobody told me there's another way. Go figure. Turns out VBA can open a web page, parse the HTML, and pull exactly what you need, no browser, no copy-paste, no cleanup:

Sub ScrapePrices()
Dim http As Object, html As Object
Dim rows As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://example-supplier.com/prices", False
http.Send
Set html = CreateObject("HTMLFile")
html.body.innerHTML = http.responseText
Set rows = html.getElementsByClassName("price-row")
For i = 0 To rows.Length - 1
Cells(i + 1, 1) = rows(i).innerText
Next i
End Sub

It hits the URL, grabs the HTML, finds every element with the class price-row, and writes each one into your sheet. The whole thing runs in under 15 seconds. What used to ruin my Friday afternoons now happens while I get coffee and chat with colleagues without feeling anxious of the remaining work that has to be done. Also, you do need to peek at the page source (F12 in your browser) to find the right class or tag to target, but that takes about 2 minutes once you know what you're looking for. Works on any site that doesn't require a login or JavaScript to load the content. If you've been manually copy-pasting from the same websites over and over, you're probably one macro away from never doing it again.

TLDR: The code uses MSXML2.XMLHTTP and HTMLFile to fetch and parse a webpage.

If y'all have further improvements, let me know, I'm fairly new to the automation world, but boy do I love it already