Problem with code using Internet Explorer

The Gunslinger

Board Regular
Joined
Dec 28, 2003
Messages
76
I have a routine which has one simple task of extracting a value from a website, the value is the current UK ISA allowance, from this web site https://www.gov.uk/individual-savings-accounts/overview

the code works a treat, with one small problem, that i only found when i used an adaption of the code to extract other data from a different page, as that code is still a work in progress, i'll post this one, which exhibits the same problem....

once run, it starts a copy of Internet Explorer, not visible as instructed, however, at the end of the code it fails to close it, leaving it orphaned and visible in task manager. I found this problem when i used the code in a loop

here's the code, can someone point out where i've gone wrong please, because right now i can't seem to see the wood for the trees.

Code:
Sub Get_ISA_Allowance()
On Error GoTo Skip
If IsInternetConnected() = True Then

    Dim Browser As InternetExplorer
    Dim Document As HTMLDocument
    Dim Elements As IHTMLElementCollection
    Dim Element As IHTMLElement

    Set Browser = New InternetExplorer
    Browser.Visible = False
    Browser.Navigate "https://www.gov.uk/individual-savings-accounts/overview"

    Do While Browser.Busy And Not Browser.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop

    Set Document = Browser.Document

    Set Elements = Document.getElementsByTagName("em")
    For Each Element In Elements
        ' Exit null value
        If Element.innerText = "" Then GoTo Skip
        ' Set ISA Allowance cell to new value, stripping out characters first
        Sheet1.Range("ISA_Allowance_Total").Value = Format(Element.innerText, "#,##")
    Next Element

Skip:
    Set Document = Nothing
    Set Browser = Nothing

End If
On Error GoTo 0
End Sub


for this code, i have also enabled the reference to "Microsoft Internet Controls" in the VBA references dialog
the internet check is also a custom function in another module that works fine

TIA
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

hateme28

Board Regular
Joined
Jul 2, 2014
Messages
161
you never tell browser to quit. you just set it to nothing. try something like this.

HTH


Code:
Skip:
Browser.Quit
    Set Document = Nothing
    Set Browser = Nothing
 

The Gunslinger

Board Regular
Joined
Dec 28, 2003
Messages
76
Doh !!!!!

that got it, i knew it was something simple, ok, thank you...... now back to my other routine where i'm looping that code, except small problem there, i think i got my ip banned from the site i was accessing for the data, probably too many requests in a given period of time, although i am having issues with error trapping as well, where resume next isnt working :(
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,477
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top