Macro not running...

[Dystar]

Hi,
I've been following WiseOwl's webscraping instructions and amending them for my own purposes. The script works without bugs (so far...) but when I try to run it out of Debug mode it doesn't seem to work, the Status Bar changes when I try to run it either through the developer menu or a button, but nothing actually happens unless I F8 all the way through the code and then it works perfectly.... Any idea what is going wrong and why it won't run?

Option Explicit

Enum READYSTATE
READYSTATE_UNINITIALIZED = 0
READYSTATE_LOADING = 1
READYSTATE_LOADED = 2
READYSTATE_INTERACTIVE = 3
READYSTATE_COMPLETE = 4
End Enum

Sub ImportHTMLFromSource(URL As String)
Dim pageCounter As Integer
Dim source As String
For pageCounter = 1 To 1000
source = URL + "allresults/" + CStr(pageCounter)
'to refer to the running copy of Internet Explorer
Dim ie As InternetExplorer
'to refer to the HTML document returne
Dim html As HTMLDocument
'open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer
ie.Visible = False
ie.navigate source
'Wait until IE is done loading page
Do While ie.READYSTATE <> READYSTATE_COMPLETE
Application.StatusBar = "Loading URL ..."
DoEvents
Loop
'show text of HTML document returned
Set html = ie.document
'close down IE and reset status bar
Set ie = Nothing
Application.StatusBar = ""

Dim ArticleList As IHTMLElementCollection
Dim Article As IHTMLElement
Dim ArticleURL As String
Dim counter As Integer
counter = SCFindLastRow(1) + 1

Set ArticleList = html.getElementsByClassName("story noThumb")
If ArticleList.Length > 0 Then
    For Each Article In ArticleList
    ArticleURL = Article.innerHTML
    Dim URLLength As Integer
    URLLength = Len(ArticleURL)
    Dim URLStart As Integer
    URLStart = InStr(ArticleURL, "http://www.nytimes.com") - 1
    ArticleURL = Right(ArticleURL, URLLength - URLStart)
    URLLength = Len(ArticleURL)
    Dim URLEnd As Integer
    URLEnd = InStr(ArticleURL, ".html") + 5
    ArticleURL = Left(ArticleURL, URLLength - (URLLength - URLEnd))
    Cells(counter, 1) = ArticleURL
    counter = counter + 1
    Next
Else
    Exit Sub
End If
Next
End Sub

Sub test()
ImportHTMLFromSource ("http://query.nytimes.com/search/sitesearch/#/house+price/from19840101to19840101/")

End Sub
Public Function SCFindLastRow(ByVal ColNum As Integer) As Integer
    SCFindLastRow = Cells(65536, ColNum).End(xlUp).Row
End Function

 

[cerfani]

what error do you get?

 

[Dystar]

what error do you get?

Thats the weird thing, I don't get an error. I've changed "ie.Visible = False" to True and when I run the macro it loads the IE window and navigates to the page then just stops and returns to the waiting state.

 

[cerfani]

...navigates to the page then just stops and returns to the waiting state.

By returns to the Waiting State do you mean the macro sub ends?

Also before writing to the status bar, check the Application.Ready property, I believe if you try to write to the status bar when the app is not ready it can end your sub, i could be wrong but worth checking out

https://msdn.microsoft.com/en-us/library/office/ff197917.aspx

 

[Dystar]

OK done a bit more digging... it seems the page isn't fully loaded when it exits the Do...While loop so when it checks whether ArticleList.Length > 0 it is going to the Else statement and exiting the sub. Are there any alternatives to this Do...While loop for waiting for the page to load?

 

[cerfani]

this may help (smozgur's post)...

http://www.mrexcel.com/forum/excel-...dystate_complete-not-working.html#post1757596