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

 

[ETID]

Have you tried an ODBC link to the web page?


from menu...

Data>get external data>new web query....follow the wizard.

 

[tbl]

For maximum amusement you could try this. I use it out of sheer laziness, (and lack of programming skills) to import data from websites.Once I've got everything in Excel I feel more at home.

Sub Read_URL_Test()

With ActiveSheet.QueryTables.Add(Connection:="URL;

http://XXXXXXXXXXXXX",

_
Destination:=Range("a1&quot)'write web-page to sheet

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

 

[RobBroekhuis]

I love it. Never knew it was so easy to get web data into excel. Now I just have to figure out a reason why I need this


Rob

 

[tbl]

I'm deeply honoured to get a smiley from you Rob !
I use this for getting info from our company online databases. It's so much less hassle than writing queries.
Otherwise its just a solution looking for a problem :->

 

[pragmatic]

This code is cool. One question though, do you know how you could send a user ID and password to a web-site that requrires login?

 

[simeybt]

you took the words outta my mouth pragmatic. this would excellent addition to an excellent piece of code

 

[Chattin]

Looks like a handy bit of code but can't get it to work (tweaked a bit though)

Sub Read_URL_Test()
Dim URL As String
URL = InputBox("Enter web address&quot
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("a1&quot) 'write web-page to sheet
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

the line With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("a1&quot) is highlighted yellow when i run it

Must be something simple i've missed - using xl2k2 and xp


Chris ;-)

Visit

http://www.mrexcel.com/board/index.php

for great xl and xl/VBA help

 

[BenyG]

Thanks for your help ETID and tbl. The code works!

I was always going to login manually and then get it to navigate to the page I want but if you can send over the pass that would be great.

Thanks,

Ben

 

[IFRs]

I must be an idiot!! I can't get it to work!! Please lead me by the nose and explain how to set this up.....

 

[VBAjedi]

Chattin/IFRs, there's an extra/misplaced semicolon in the example (after the closing quote of the URL). Try:

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

Nice frag, tbl! I'm with Rob - makes me wanna make something with it! Have a star. . .

VBAjedi

 

[VBAjedi]

pragmatic,

Haven't tested this, but I've read that some sites that require username/password login can be reached using a URL in the form:

[URL unfurl="true"]http://username:password@www.mywebsite.com/optionalpage[/URL]

Try plugging that into tbl's code frag for your website. I'd be interested to hear if it works! (I tried it on my yahoo mail account just for the heck of it, but no joy. That's probably a special setup, though!).

VBAjedi

 

[pragmatic]

I'll give that a try, thanks.

 

[IFRs]

This is way cool !!!
Is there a way for it to be changed so it grabs the currently displayed web site on the default browser?

 

[tbl]

Sorry to have missed all the fun, but I guess most of those who reacted are not in Europe, and we do like to sleep sometimes

To send a password or piece of text I stick this bit into my code after the connect line.

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

Richard

 

[simeybt]

Is it also possible to grab the currently open page? I take it you can send any info you want like this for example search criteria, not just passwords and usernames.

Simon

 

[simeybt]

As IFRs says,
Is there a way for it to be changed so it grabs the currently displayed web site on the default browser. I have a site that you cannot get to by supplying the post information. So i need to link to it manually the pick up the information off the page

 

[BenyG]

VBAjedi,

What you are referring to is for ftp sites and can thus only be used with ftp://

It *may* work for sites that use the login feature of http without creating a form on the page. - This is where the login comes up in a seperate window if you know what I mean.

 

[simeybt]

I used this post previously to great effect, but I have now been trying to get the postdata section of the code to work (tbl's example code) so I can send information to a page to get the results back, if anyone who has used this successfully it would be greatly appreciated if you could share your code with me. and example of passing multiple values would be excellent as I cant work out tbl’s example

Thanks

 

[wotgoesup]

I have managed to login to a password protected site using the sub below - but this gets me to a screen that has three folder tabs. Anyone know how to simulate clicking to choose one of these tabs?

Sub LoginMe()

Dim appIE As Object

Set appIE = CreateObject("InternetExplorer.Application")
appIE.Visible = True

While appIE.busy
DoEvents
Wend

appIE.navigate "

http://urlblahblah"

While appIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Wend

SendKeys "userID", True
SendKeys "{TAB}", True
SendKeys "pswd", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True

End Sub

The number of tabs are what's required to get to the relevant entries.