Excel VBA - Extracting info from Web pages

[superta]

I'm a VBA newbie who needs to use Excel VBA to:
- Access and logon to a website
- Select various reports available from the website by clicking on selections and entering selection criteria
- Capturing the reports displayed by the website into my Excel spreadsheets.

I've looked at the tek-tips forums, and found how to open the website, but don't see how to do the rest of the above.

I'd appreciate any suggestions.

 

[shaddow]

You would need a browser type Application (you can use WebBrowser control) and only then you can simulate a link click or something.

________
George, M

 

[superta]

By "browser type Application" do you mean something like Internet Explorer? More details please.

Thanks.

 

[shaddow]

Yes, You would need an active X that would use the WebBrowser control(wich is the IE engine).
I've used with Visual Basic and it works. If you load a page you have the reference to the document object model(DOM).

________
George, M

 

[superta]

Noted. Any sample code available to help me get started? I'm still not clear on how to use the website's objects (from Excel VBA) once I get to the website.

Thanks.

 

[Mikeb1970]

Most webpages have some logic that can easely be found when looking at some links.

example :

http://finance.yahoo.com/q/hp?s=MSFT

will retreive historic stockdata for microsoft

http://finance.yahoo.com/q/hp?s=yhoo

will retrieve historic stockdata for yahoo

in most cases the logic can be found, and the link needed can be created using some concentating, and then using a webquery it should get you the info into excel pretty fast

 

[mjbeam]

If the webpage uses java objects you can usually view the source code for the webpage and set the properties for the objects and submit the page. Here is some code I wrote to lookup currency conversion rates at a the oanda website,

http://oanda.com/convert/fxhistory.

It uses the IE Webbrowser control:


Dim navdone As Boolean
Dim mdata As String

Private Sub UserForm_Activate()

Do
DoEvents
Loop Until navdone = True

Do
DoEvents
Loop Until InStr(WebBrowser1.Document.documentelement.innerhtml, &quot;FXHistory ©1997-2003 by OANDA Corporation&quot <> 0

Currency1 = &quot;USD&quot;
Currency2 = &quot;AUD&quot;

Startdate = &quot;08/01/03&quot;

WebBrowser1.Document.all(&quot;exch2&quot.Value = Currency1
WebBrowser1.Document.all(&quot;expr2&quot.Value = Currency2
WebBrowser1.Document.all(&quot;date1&quot.Value = Startdate

navdone = False
WebBrowser1.Document.all(&quot;SUBMIT&quot.Click

Do
DoEvents
Loop Until navdone = True

Do
DoEvents
Loop Until InStr(WebBrowser1.Document.documentelement.innerhtml, &quot;Conversion Table:&quot <> 0

mydata = WebBrowser1.Document.documentelement.innerhtml

'Add code to parse your currency exchange data from the mydata string here

End Sub

Private Sub UserForm_Initialize()

navdone = False

WebBrowser1.Navigate &quot;

http://oanda.com/convert/fxhistory&quot;

End Sub

Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant)

navdone = True

End Sub


These lines are where the three textboxes are filled with the correct info and the submit button is clicked:


WebBrowser1.Document.all(&quot;exch2&quot.Value = Currency1
WebBrowser1.Document.all(&quot;expr2&quot.Value = Currency2
WebBrowser1.Document.all(&quot;date1&quot.Value = Startdate

navdone = False
WebBrowser1.Document.all(&quot;SUBMIT&quot.Click


If you look at the source code for this webpage you can see where these objects are defined.


-Mike