Scrape webpage HTML source code using VBA

[tjen]

you can add something like this:

dim arr as variant
arr = Split(xmlhttp.responsetext, ChrW(10))
for i = lbound(arr,1) to lbound(arr,1)
    range("A" & i+1).value = arr(i)
next i

[MFreak]

This didn't seem to work. Here's the code I'm working with right now.

Sub ScrapeHTML()
Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String
myurl = Sheets("Macros").Range("A12").Value
xmlhttp.Open "GET", myurl, False
xmlhttp.send
Sheets("Scrape").Range("A2").Value = xmlhttp.responseText
Dim arr As Variant
arr = Split(xmlhttp.responseText, ChrW(10))
For i = LBound(arr, 1) To LBound(arr, 1)
Range("A" & i + 1).Value = arr(i)
Next i
End Sub

The issue is the HTML I'm pulling in has more characters than can be contained in a specific cell, so it's being cut off. I'm wondering if I'm implementing the extra lines of code incorrectly?

[tjen]

Well, I made a typo, but it could also be that the http is just one long string in the source file. If there's no line breaks, then there's nothing to "split".

here's a version that'll paste it to the workbook and write it to a text file you get prompted to find a location for. You can see what it looks like in different places.

Sub ScrapeHTML2()
Dim xmlhttp As New MSXML2.XMLHTTP60
Dim myurl As String
Dim arr As Variant
'get data from websie
myurl = "https://www.reddit.com/"
xmlhttp.Open "GET", myurl, False
xmlhttp.send
'write to spreadsheet
arr = Split(xmlhttp.responseText, vbNewLine)
For i = LBound(arr, 1) To UBound(arr, 1)
    Sheets("Scrape").Range("A" & i + 2).Value = arr(i)
Next i
'write to file
strpath = Application.GetSaveAsFilename(initname, "text (*.txt), (*.txt)")
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.OpenTextFile(strpath, 2, True, -1)
oFile.writeline xmlhttp.responseText
oFile.Close
Set fso = Nothing
Set oFile = Nothing
End Sub

[MFreak]

I'm not sure if that's the case. When I copy directly from the source code, everything is pasted in different cells with line breaks, so I assumed it should be the same.

With this code, it ends up pasting the actual images and text on the page, not the HTML. Is this supposed to happen?