Repeated call to InternetExplorer.Application seems to hang the PC

H

HC

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, "¹L©¹¦¨¥æ¬ö¿ý") > 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, "³æ¦ì­±¿n") + 6, InStr(1,
MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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, "³æ¦ì­±¿n") +
6, InStr(1, MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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, "¸U) - 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, "¸U") -
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
 
D

David H

Although you are trying to automate IE from Access, you're still working with
IE. The question that you've asked is out of scope for pretty much everyone
here unless there just happens to by someone with prior experience, but
that's highly unlikely.

I can tell you that when you're dealing with content in a web browser that
it isn't enough to just navigate to a new URL, you also have to wait for the
content to be loaded just you're viewing the page in person. I've been
developing an ASP.NET project and had to factor that in myself.

In terms of the IE issue, try posting in a forum specific to web development
such as http://www.webdeveloper.com/ as you might be able to find someone
there with experience dealing with IE and DOM events. Its highly unlikely
that you'll find a plethora of people familiar with Office Automation but you
never know. If you're not able to find help there, you're going to have to
google around for an appropriate newsgroup/forum. You will most likely have
to look for general information dealing with DOM and then try to figure out
yourself how to apply that to IE.

I *think* that the IE object model *might* expose a good number of the DOM
events so the ondocumentready *might* be able to be used to detect that the
document is fully loaded, however I have no idea how you'd check for.
http://msdn.microsoft.com/en-us/library/ms531024(VS.85).aspx

You could try the following groups however they are more geared toward
Office Automation which typically deals with the Office applications per se
http://www.microsoft.com/office/com...soft.public.office.developer.vba&lang=en&cr=U
http://www.microsoft.com/office/com...lic.office.developer.automation&lang=en&cr=US

Also, you do realize that 'For Each itm In IE.Document.all' is going to loop
through every single element on a page and that depending on the design of
the page that you might be dealing hundreds of elements? The ramifications of
that is that the code will take time to execute. Also, given the
document.getElementById is the standard means of referring to elements on a
page, there's no guarantee that Microsoft will continue to implement in
future versions of IE. It might actually be gone in the later versions of IE.

So in terms of performance, you've got at least two bottlenecks. 1) The
amount of time it takes for a page to load which is impacted by the speed of
your network connection and traffic. 2) The number of individual elements on
a page, which impacts the amount of time it takes to load a page as well as
for the code to read the page.

Also, part of the problems that you've been having is most likely tied to
instances of IE being created that are never destroyed. You're effectively
starting IE, if you bail out of the code before you kill it using SET IE =
NOTHING the instance of IE will be left running. When you break out of the
code and intend to kill it, manually kill IE by entering SET IE = NOTHING.
You should also do the same for the RecordSet object manually executing
rst.CLOSE followed by SET RS = Nothing. The rule to allows close what you
open and destroy what you create also applies when you manually kill the code.


HC said:
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
Click to expand...
 
D

DeZZar

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, "¹L©¹¦¨¥æ¬ö¿ý") > 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, "³æ¦ì­±¿n") + 6, InStr(1,
MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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, "³æ¦ì­±¿n") +
6, InStr(1, MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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, "¸U) - 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, "¸U") -
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
Click to expand...

Perhaps check that after you run the code once Internet Explorer
actually closes.

You have Set IE = Nothing in the cleanup of your module but perhaps it
not actually closing off the IE session and the PC is hanging becuase
there are really 20+ active instances of IE.

Check your processes next time it hangs and have a look at how many
IE's are running...
 
L

lyle fairfield

HC said:
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, "¹L©¹¦¨¥æ¬ö¿ý") > 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, "³æ¦ì­±¿n") + 6,
InStr(1, MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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,
"³æ¦ì­±¿n") +
6, InStr(1, MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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, "¸U) - 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,
"¸U") -
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
Click to expand...

I use a webbrowser control to grab web info when in Access:

While Not .EOF

WayPoint = .Collect(0)

With WebBrowser0

.Navigate GeocacheURI(WayPoint)

DoEvents

CacheName = .Document.getElementById("CacheName").innerText
CacheOwner = .Document.getElementById("CacheOwner").innerText
LatLon = .Document.getElementById("LatLon").innerText
ShortDescription = .Document.getElementById
("ShortDescription").innerText
LongDescription = .Document.getElementById("LongDescription").innerText
Hints = .Document.getElementById("Hints").innerText
CacheLogs = .Document.getElementById("CacheLogs").innerText
 
D

David H

If you're using the ActiveX Web Browser control, the line

Set IE = CreateObject("InternetExplorer.Application")

is not neccessary as the line is actually instantiating an instance of IE,
like you double clicked the icon manually. The WEb Browser Control will
handle all of that on your behalf.

You should be able to access the content of the control using syntax similar
to

controlname.somepropertyname.etc.etc

I've never worked with that control so I can't speak as to what the specific
syntax is. You'll need to google around for it.

lyle fairfield said:
HC said:
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, "¹L©¹¦¨¥æ¬ö¿ý") > 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, "³æ¦ì­±¿n") + 6,
InStr(1, MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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,
"³æ¦ì­±¿n") +
6, InStr(1, MyString, "§`") - InStr(1, MyString, "³æ¦ì­±¿n") - 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, "¸U) - 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,
"¸U") -
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
Click to expand...

I use a webbrowser control to grab web info when in Access:

While Not .EOF

WayPoint = .Collect(0)

With WebBrowser0

.Navigate GeocacheURI(WayPoint)

DoEvents

CacheName = .Document.getElementById("CacheName").innerText
CacheOwner = .Document.getElementById("CacheOwner").innerText
LatLon = .Document.getElementById("LatLon").innerText
ShortDescription = .Document.getElementById
("ShortDescription").innerText
LongDescription = .Document.getElementById("LongDescription").innerText
Hints = .Document.getElementById("Hints").innerText
CacheLogs = .Document.getElementById("CacheLogs").innerText
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repeated call to InternetExplorer.Application seems to hang the PC 0

Top