Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA - Extracting info from Web pages

Status
Not open for further replies.

superta

Technical User
Nov 10, 2003
3
US
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.
 
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
 
By "browser type Application" do you mean something like Internet Explorer? More details please.

Thanks.
 
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
 
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.
 
Most webpages have some logic that can easely be found when looking at some links.

example :


will retreive historic stockdata for microsoft


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
 
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, 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;
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top