VBA to Scrap Webite

[PShingadia]

Hi:

Just trying to go though some VBA to scrap a website and having issues with it picking up values during the looping shown in red below. It seems to skip this and go to the end of the code. Any help would be gratefully received!

Sub ImportStackOverflowData()


Dim QuestionList As IHTMLElement
Dim Questions As IHTMLElementCollection
Dim Question As IHTMLElement
Dim RowNumber As Long
Dim QuestionId As String
Dim QuestionFields As IHTMLElementCollection
Dim QuestionField As IHTMLElement
Dim votes As String
Dim views As String
Dim QuestionFieldLinks As IHTMLElementCollection
Dim IE As InternetExplorer
Dim html As HTMLDocument

Set IE = New InternetExplorer
IE.Visible = False
IE.Navigate "http://stackoverflow.com/"
Do While IE.ReadyState <> READYSTATE_COMPLETE
Application.StatusBar = "Trying to go to StackOverflow ..."
DoEvents
Loop


Set html = IE.Document
Set IE = Nothing

Application.StatusBar = ""


Cells.Clear

Range("A3").Value = "Question id"
Range("B3").Value = "Votes"
Range("C3").Value = "Views"
Range("D3").Value = "Person"

Set QuestionList = html.getElementById("question-mini-list")
Set Questions = QuestionList.Children
RowNumber = 4

For Each Question In Questions

If Question.className = "question-summary narrow" Then

QuestionId = Replace(Question.ID, "question-summary-", "")
Cells(RowNumber, 1).Value = CLng(QuestionId)

Set QuestionFields = Question.all

For Each QuestionField In QuestionFields


If QuestionField.className = "votes" Then
votes = Replace(QuestionField.innerText, "votes", "")
votes = Replace(votes, "vote", "")
Cells(RowNumber, 2).Value = Trim(votes)
End If


If QuestionField.className = "views" Then
views = QuestionField.innerText
views = Replace(views, "views", "")
views = Replace(views, "view", "")
Cells(RowNumber, 3).Value = Trim(views)
End If

If QuestionField.className = "started" Then
Set QuestionFieldLinks = QuestionField.all
Cells(RowNumber, 4).Value = QuestionFieldLinks(2).innerHTML
End If
Next QuestionField

RowNumber = RowNumber + 1

End If

Next

Set html = Nothing

Range("A3").CurrentRegion.WrapText = False
Range("A3").CurrentRegion.EntireColumn.AutoFit
Range("A1:C1").EntireColumn.HorizontalAlignment = xlCenter
Range("A1:D1").Merge
Range("A1").Value = "StackOverflow home page questions"
Range("A1").Font.Bold = True

Application.StatusBar = ""

MsgBox "Done!"

End Sub

 

[John_w]

Why do you want to scrap it? Seems a bit drastic. Have you got permission from the owner?

 

[PShingadia]

Oops!! Sorry for spelling. Posted in a rush before going out.