1

I've written some VBA script to load a webpage then copy the entire html contents into a string, then select specific data from that string. In essence I search for a rail timetable, then copy out details for 5 journeys (departure time, interchanges, journey time & cost)

I have the above script sorted to do one search, but I now want to loop it and run approximately 300 searches. The issue I've found is that the script won't wait for the webpage to open, and therefore the string returned is empty, effectively returning nothing.

What I need to do is load an address, wait for the page to load, then continue the script. Do you have any suggestions? I've searched a lot and just haven't been able to sort, I've tried Application.Wait in a number of places and still no further ahead.

The code I'm using is below:

Sub CreateIE()
Dim tOLEobject As OLEobject
Dim NRADDRESS As String
NRADDRESS = Range("h11")
On Error Resume Next
Worksheets("Sheet1").Shapes.Range(Array("WebBrow")).Delete
Set tOLEobject = Worksheets("Sheet1").OLEObjects.Add(ClassType:="Shell.Explorer.2",Link:=False, _
DisplayAsIcon:=False, Left:=0, Top:=15, Width:=912, Height:=345)

For Each tOLEobject In Worksheets("Sheet1").OLEObjects
If tOLEobject.Name = "WebBrowser1" Then
With tOLEobject
.Left = 570
.Top = 1
.Width = 510
.Height = 400
.Name = "WebBrow"
End With


With tOLEobject.Object
.Silent = True
.MenuBar = False
.AddressBar = False
.Navigate NRADDRESS
End With
End If
Next tOLEobject

Sheets("Sheet2").Activate
Sheets("Sheet1").Activate

Call ReturnText
End Sub

NRADDRESS is a web address made up of a number of different parameters (origin, destination, date and time)

The "Call ReturnText" is the script I use to copy the website HTML into a string and extract what I want.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
user3533028
  • 9
  • 1
  • 5
  • possible duplicate of [VBA: Inconsistent error 91 in loop w/ IE.doc reference](http://stackoverflow.com/questions/21335370/vba-inconsistent-error-91-in-loop-w-ie-doc-reference) – David Zemens May 13 '14 at 18:24
  • Please try the solution linked above, use the ready waiting loop in conjunction with the WinAPI `Sleep` function. You should also get rid of `On Error Resume Next`, this statement alone will make your code difficult to debug, as errors tend to lead to more errors... – David Zemens May 13 '14 at 18:26

2 Answers2

2

In that case, you might try something like this:

Set objIE = CreateObject("InternetExplorer.Application")
objIE.navigate strURL

Do While objIE.readyState <> 4 And objIE.Busy
   DoEvents
Loop

which, I believe, requires a reference to Microsoft Internet Controls.

Michael Blaustein
  • 901
  • 6
  • 24
  • This approach should work with late-binding, no required reference to the MS Internet Controls. I would recommend using the WinAPI Sleep function instead of `DoEvents`. See [here](http://stackoverflow.com/questions/21335370/vba-inconsistent-error-91-in-loop-w-ie-doc-reference/) for example. – David Zemens May 13 '14 at 18:27
  • Thanks for this. I was looking at something similar yesterday but just couldn't get it to work. Stripped it back to bare essentials to get the `Do While objIE.readyState <> 4 And objIE.Busy` line to work and then built it back up again. I'm very much new to this! – user3533028 May 14 '14 at 09:20
1

When I first started using VBA to load webpages, I also used the IE Object, but later found it creates all kinds of complications I didn't need, when all I really wanted was to download the file. Now I always use URLDownloadToFile.

A good example of it's use can be found here:

VBA - URLDownloadToFile - Data missing in downloaded file

Community
  • 1
  • 1
Michael Blaustein
  • 901
  • 6
  • 24
  • Thanks for the reply. I've just tested your method and it would mean rewriting the 'Rettext' script as it doesn't come out exactly the same as document.body.innerhtml I'm confident what I want to do is possible - hopefully someone here can point me in the correct direction. – user3533028 May 13 '14 at 15:29