rockmuncher
asked on
Microsoft Internet Controls problem
Every so often the following code breaks, showing
Run-time error '91': Object variable or With block variable not set
It's like the InternetExplorer control doesn't complete the task in time. If I choose the Debug option then continue execution everything is fine!
Any ideas on what is happening and how to fix it?
Private Sub btnGo_Click()
'On Error GoTo Err_btnGo_Click
' requires Microsoft HTML Object Library
' requires Microsoft Internet Controls
Dim strHTML As String
Dim i As Long
Dim item As String
Dim posted As String
Dim points As String
Dim Qtitle As String
Dim Qhref As String
Dim Mtitle As String
Dim Mhref As String
Dim appIE As New InternetExplorer
Dim docHTML As HTMLDocument
DoCmd.Hourglass True
appIE.Navigate2 "https://www.experts-exchange.com/Databases/MS_Access/"
'Wait while the page is downloading
While appIE.Busy
DoEvents
Wend
Set docHTML = appIE.Document
Me.Refresh ' I'm just trying to waste a micro amount of time here
DoEvents
i = InStr(docHTML.body.innerHT ML, "Questions Awaiting Answers") ' <--- this line fails every so often. Debug it and I can continue with no errors or data loss
i = InStr(i, docHTML.body.innerHTML, "</TABLE>") + 8
i = InStr(i, docHTML.body.innerHTML, "<TD class=itemOdd align=right>")
strHTML = Mid(docHTML.body.innerHTML , i, i - InStr(docHTML.body.innerHT ML, "</TABLE>"))
me.txtField=strHTML
Exit_btnGo_Click:
DoCmd.Hourglass False
Exit Sub
Err_btnGo_Click:
DoCmd.Hourglass False
Resume Exit_btnGo_Click
End Sub
Run-time error '91': Object variable or With block variable not set
It's like the InternetExplorer control doesn't complete the task in time. If I choose the Debug option then continue execution everything is fine!
Any ideas on what is happening and how to fix it?
Private Sub btnGo_Click()
'On Error GoTo Err_btnGo_Click
' requires Microsoft HTML Object Library
' requires Microsoft Internet Controls
Dim strHTML As String
Dim i As Long
Dim item As String
Dim posted As String
Dim points As String
Dim Qtitle As String
Dim Qhref As String
Dim Mtitle As String
Dim Mhref As String
Dim appIE As New InternetExplorer
Dim docHTML As HTMLDocument
DoCmd.Hourglass True
appIE.Navigate2 "https://www.experts-exchange.com/Databases/MS_Access/"
'Wait while the page is downloading
While appIE.Busy
DoEvents
Wend
Set docHTML = appIE.Document
Me.Refresh ' I'm just trying to waste a micro amount of time here
DoEvents
i = InStr(docHTML.body.innerHT
i = InStr(i, docHTML.body.innerHTML, "</TABLE>") + 8
i = InStr(i, docHTML.body.innerHTML, "<TD class=itemOdd align=right>")
strHTML = Mid(docHTML.body.innerHTML
me.txtField=strHTML
Exit_btnGo_Click:
DoCmd.Hourglass False
Exit Sub
Err_btnGo_Click:
DoCmd.Hourglass False
Resume Exit_btnGo_Click
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Should be (to be clear)
Set docHTML = appIE.Document
Dim dtStart As Date
Dim dtnow As Date
Dim lWait As Long
lWait = 2 ‘pause for 2 seconds
dtStart = Now
dtnow = dtStart
While dtnow < DateAdd("s", lWait, dtStart)
DoEvents
dtnow = Now()
Wend
i = InStr(docHTML.body.innerHT ML, "Questions Awaiting Answers") ' <--- this line fails every so often. Debug it and I can continue with no errors or data loss
i = InStr(i, docHTML.body.innerHTML, "</TABLE>") + 8
i = InStr(i, docHTML.body.innerHTML, "<TD class=itemOdd align=right>")
strHTML = Mid(docHTML.body.innerHTML , i, i - InStr(docHTML.body.innerHT ML, "</TABLE>"))
Set docHTML = appIE.Document
Dim dtStart As Date
Dim dtnow As Date
Dim lWait As Long
lWait = 2 ‘pause for 2 seconds
dtStart = Now
dtnow = dtStart
While dtnow < DateAdd("s", lWait, dtStart)
DoEvents
dtnow = Now()
Wend
i = InStr(docHTML.body.innerHT
i = InStr(i, docHTML.body.innerHTML, "</TABLE>") + 8
i = InStr(i, docHTML.body.innerHTML, "<TD class=itemOdd align=right>")
strHTML = Mid(docHTML.body.innerHTML
Not nice I know, but couldn't get rocki's to work.
Long time no see budy.. How you lovin' the soccer??
Long time no see budy.. How you lovin' the soccer??
Morning Dave, how ya doing,
Like your method for sleeping, not seen that one before
Dave, u may find it easier to use the sleep API call
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
then just call Sleep passng in number of milliseconds
Right Im off to work now, see ya guys later
Like your method for sleeping, not seen that one before
Dave, u may find it easier to use the sleep API call
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
then just call Sleep passng in number of milliseconds
Right Im off to work now, see ya guys later
Easest way I could think of doing it, used it for some VBScript to pause {couldn’t be bothered playing around with it, plus debug isnt that great}, upgraded all by batch files a few weeks ago.
Morning Dave,
Back at work, cancelled train, taxi to take me to next station delayed, therefore missed connection
its now 10:19am, I left the house at 7.10am
great start to the morning!
England got robbed, they actually scored a goal but was disallowed, dont know why, I reckon the swiss referee bottled it, England scored against the host nation in the last few mins of the game
needless to say, England lost on penalties - so lost a bit of interest now,
Tennis fever now, go on Henman!
Also speedway, top 2 riders are both aussies at the moment
Jason Crump and Leigh Adams
Back at work, cancelled train, taxi to take me to next station delayed, therefore missed connection
its now 10:19am, I left the house at 7.10am
great start to the morning!
England got robbed, they actually scored a goal but was disallowed, dont know why, I reckon the swiss referee bottled it, England scored against the host nation in the last few mins of the game
needless to say, England lost on penalties - so lost a bit of interest now,
Tennis fever now, go on Henman!
Also speedway, top 2 riders are both aussies at the moment
Jason Crump and Leigh Adams
ASKER
Thanks for the posts guys, I had already implemented time delayed retries with success. But I only consider that a workaround.
I was kinda hoping that someone might have known someone who saw a post on their brothers girlfriends blog that explained portential problems with the Internet Explorer Controls and HTML Object Libraries, perhaps even a reference to a Microsoft KB article.
I'll leave the Q for until tomorrow then split points amongst yose both (seeing as how you've put a deal of effort in) unless some young buck comes and steals them away from ya :)
I was kinda hoping that someone might have known someone who saw a post on their brothers girlfriends blog that explained portential problems with the Internet Explorer Controls and HTML Object Libraries, perhaps even a reference to a Microsoft KB article.
I'll leave the Q for until tomorrow then split points amongst yose both (seeing as how you've put a deal of effort in) unless some young buck comes and steals them away from ya :)
rockmuncher,
I was doing another Q with the htmldoc and i discovered this "real" way of doing it... i remembered this Q so i thought id post it here
rather than my dodgey wait method use
Set docHTML = appIE.Document
'wait for it
Do Until docHTML.readyState = "complete"
DoEvents
Loop
'its there now, yay!!!
Dave
I was doing another Q with the htmldoc and i discovered this "real" way of doing it... i remembered this Q so i thought id post it here
rather than my dodgey wait method use
Set docHTML = appIE.Document
'wait for it
Do Until docHTML.readyState = "complete"
DoEvents
Loop
'its there now, yay!!!
Dave
ASKER
Sorry Dave, your last post must have slipped past sleep deprived eyes (Olympics in OZ are a late night/all night event, very bad for werk). I'll give it a burl.
hey,
Where abouts in Oz, im in Brisbane.
Where abouts in Oz, im in Brisbane.
ASKER
Sydney, home of the last day-time Olympics!
I've been getting up at about 3 to see them.... starting to catch up with me...
ASKER
I'll send you some matches
ASKER
That last post of yours works fine Dave.
What made you come back to a closed Q some two months later?????
What made you come back to a closed Q some two months later?????
Set appIE = New InternetExplorer
before this
appIE.Navigate2 "https://www.experts-exchange.com/Databases/MS_Access/"
Dave