Extract text from IE into Excel 7

[BenyG]

Hi everyone,

I've tried looking around on the Internet and here but have found no solution to my problem.

I was wondering if it was possible to extract a piece of text from a web page in Internet Explorer and get it into a variale / cell. The title of the window is constant as is IE's path. I would need a parameter for some 'proceeding text' which always appears just before the text I want to extract.

Background:
I am creating a programme to manage CC accs and other things and would like Excel when asked to load up IE and goto to the 'Internet banking' website and extract the data like this: Find "Your balance is: £" and extract the next 5 digits. A perfect solution would be to extract digits up to "." and then the next 2 to account for balances > £99.99 .

I have some idea that it can be achieved by setting up a 'WebBrowser' control but I have had no luck with this so far.

Thanks for any help,

Ben

 

[VBAjedi]

You'll probably have to get into the page's HTML code and try to figure out what the desired tab's name is (it's probably actually a button or a table cell). I've gotten stuff like this to work before, but it's pretty much trial and error, and tends to break when the site gets changed.

VBAjedi

 

[SharonMee]

Hi, I don't understand what this means:

"",""Enter up to 20 symbols separated " & "by spaces.""" in the line TBL suggested for entering passwords:


.PostText = "QUOTE0=""QUOTE0"",""Enter up to 20 symbols separated " & "by spaces."""


What is QUOTE0, is that my password? Say my username is:
SharonM, and my password is: MilleySS1

Thanks a lot.

I think this will be very helpful and make work easier.

 

[vaneagle]

Hi,

This appears to be great if I could get the code to work!!
Do I need a reference added or something?
It keeps giving me a " Run-time error '1004': Application-defined or object defined error"

what am I doing wrong? or is it to do with the site security?

the code as is posted above

Sub Read_URL_Test()

          With ActiveSheet.QueryTables.Add(Connection:="URL;[URL unfurl="true"]http://XXXXXXXXXXXXX",[/URL] _
              Destination:=Range("a1"))'write web-page to sheet

              .BackgroundQuery = True
              .TablesOnlyFromHTML = False
              .Refresh BackgroundQuery:=False
              .SaveData = True
          End With
      End Sub

when I compile it gives no errors but when I step through it errors.
regards,
vaneagle

 

[jwelder2002]

In VBA then how do you reference a HTML textbox to enter a password? I know the name of the input box tag, just don't know how to reference it.

 

[pbrodsky]

I got this code (borrowed after much searching on the web)to work. The "elements(4)" was found by trial and error. The field names ("username","password") were found by looking at the html source.

Set oForm = objIE.Document.Forms(0)
DoEvents
oForm.elements("userName").Value = "(userid here)"
DoEvents
oForm.elements("password").Value = password
DoEvents
oForm.elements(4).Click

I have a feeling that there's an eaier way to do this, my I had limited time to get something working.

 

[gwgeller]

awesome code tbl, this is so much easier than what I was going to originally do. Thanks, thanks, thanks. I like the comment about sheer laziness. I think the best programmers are lazy because they find the most efficient way to do things so they don't have to work so hard!!!