Repeated call to InternetExplorer.Application seems to hang the PC

[Herbert]

Hello,

I'm trying to use Microsoft Access 2007 in Windows XP SP2 to access a
certain real estates website and extract certain transaction records for
tracking the housing prices.

I have written the macro below to do it and it has been successful.

However, I experience 2 problems (1 big and 1 small).

Big problem: As you can see, I initiate an IE application to access the
website. After running the macro like for the 20th iteration, i.e., after
reloading the IE for around 20 times with

IE.Navigate2 URL

The PC begins to hang, and fails to respond. I can see it because I have
debug.print to monitor its progress. After like a minute or two with the
program not debug.print anything, I ctrl-alt-del to call up the Windows Task
Manager to close Access. After closing Access, Windows still acts quite
weird and is not really responding. I at first thought I've hurt the
harddisk too much by the frequent refreshing of IE and it's causing damage to
the Windows. Only after like 2nd or 3rd reboot do I get the normal windows
system again. It's too scary and I do not dare to run the macro again. Does
any one know what I can do to make the macro run more smoothly without
causing damage to my PC?

Small problem: I notice that even though URL has been provided with a new
value, "IE.Navigate2 URL" seems not to be accessing the new page with a
different unit (strUnit). Anyone knows what I can do to make sure
IE.Navigate2 will indeed navigate to a new page before proceeding?

I hope someone can tell me how to solve at least the Big Problem.

Thank you very much.

Herbert

=============================

Sub HousePrices()
Dim URL, strBldg, strUnit As String
Dim IE, itm As Object
Dim MyString As String
Dim intEnd, intDummy As Integer
Dim dbs As Database, rst As Recordset
Dim rstUnit As Recordset

Set dbs = CurrentDb
Set rstUnit = dbs.OpenRecordset("tblUnit")
Set rst = dbs.OpenRecordset("tblPrice")

Set IE = CreateObject("InternetExplorer.Application")

'Set my_object = CreateObject("htmlfile")
'Set IE = New InternetExplorer
'IE.Visible = True
rstUnit.MoveFirst
With rstUnit
Do While Not .EOF
strBldg = !BldgID
strUnit = !UnitID
URL = "http://proptx.midland.com.hk/unit/index.jsp?bldg_id=" &
strBldg & "&unit_id=" & strUnit
Debug.Print URL
IE.Navigate2 URL
Do
DoEvents
Loop Until IE.ReadyState = READYSTATE_COMPLETE

'Sheets("Sheet2").Range("a2").Value =
IE.document.getElementsByTagName("HTML").innertext
For Each itm In IE.Document.all
If InStr(1, itm.innertext, "éŽå¾€æˆäº¤ç´€éŒ„") > 0 Then
MyString = itm.innertext
'Debug.Print MyString
Exit For
End If
' .Range("A" & RowCount) = itm.tagname
' .Range("B" & RowCount) = itm.ID
' .Range("C" & RowCount) = itm.classname
' .Range("D" & RowCount) = Left(itm.innertext, 1024)
' RowCount = RowCount + 1
Next itm
'Debug.Print InStr(1, MyString, DLookup("[BldgName]", "tblBuilding",
"[BldgID]='" & strBldg & "'"))
Debug.Print Trim(Mid(MyString, InStr(1, MyString, "樓") - 2, 2)) &
Trim(Mid(MyString, InStr(1, MyString, "室") - 1, 1)),
StripComma(Trim(Mid(MyString, InStr(1, MyString, "å–®ä½é¢ç©") + 6, InStr(1,
MyString, "å‘Ž") - InStr(1, MyString, "å–®ä½é¢ç©") - 6)))

dbs.Execute "UPDATE tblUnit SET Floor = " & Trim(Mid(MyString,
InStr(1, MyString, "樓") - 2, 2)) & _
", Flat = '" & Trim(Mid(MyString, InStr(1, MyString, "室") - 1,
1)) & "', Area = " & _
StripComma(Trim(Mid(MyString, InStr(1, MyString, "å–®ä½é¢ç©") +
6, InStr(1, MyString, "å‘Ž") - InStr(1, MyString, "å–®ä½é¢ç©") - 6))) _
& " WHERE UnitID = '" & strUnit & "';"

intEnd = InStr(1, MyString, "--")
'Debug.Print intEnd
intDummy = InStr(1, MyString, "å”®")
'Debug.Print intDummy
While intDummy <> 0 And intDummy < intEnd
'Debug.Print Trim(Mid(MyString, InStr(intDummy, MyString, "å”®) -
8, 8))
'Debug.Print Trim(Mid(MyString, InStr(intDummy, MyString, "è¬) -
4, 4))
rst.AddNew
rst!UnitID = strUnit
rst!TransDate = InterpretDate(Trim(Mid(MyString, InStr(intDummy,
MyString, "å”®") - 8, 8)))
rst!Price = Trim(Mid(MyString, InStr(intDummy, MyString, "è¬") -
4, 4))
rst.Update
intDummy = InStr(intDummy + 1, MyString, "å”®")
' Debug.Print intDummy
Wend
.MoveNext
Loop
End With
rstUnit.Close
rst.Close
Set IE = Nothing
Set dbs = Nothing

'Set my_object = Nothing
End Sub