obtaining values from a web page

[Pete]

I have figured out how to activate IE, open an URL, but I
need to pull a particular value from the page. Here is the
web page I'm accessing followed by the code I am using. I
need the "CURRENT FLOW" value which is about 3/4 the way
down the web page on the left side.



http://waterdata.usgs.gov/mt/nwis/uv/?
site_no=06191500&PARAmeter_cd=00060,00065,00010


Private Sub CommandButton1_Click()
Dim ie As Object
Dim result As Integer
Dim myRow As Integer, myCol As Integer
Dim myURL As String
'
' Get activecell value, must be a valid
' web address
'
myRow = ActiveCell.Row
myCol = ActiveCell.Column
myURL = ActiveSheet.Cells(myRow, myCol)
'
' Set up the Automation object
'
Set ie = CreateObject("InternetExplorer.Application")
'
' Navigate to a page and customize the browser window
'
ie.Navigate _
"http://waterdata.usgs.gov/mt/nwis/uv/?
site_no=06191500&PARAmeter_cd=00060,00065,00010"
ie.Toolbar = True ' }set these to true if you want to
have the
ie.StatusBar = True ' }toolbar, statusbar and menu
visible
ie.MenuBar = True ' }
'
' keep itself busy while the page loads
'
Do While ie.Busy
Do While ie.Busy
DoEvents
Loop
Loop
'
' Display page info
'
result = MsgBox( _
"Current URL: " & ie.LocationURL & vbCrLf & _
"Current Title: " & ie.LocationName & vbCrLf & _
"Document type: " & ie.Type & vbCrLf & vbCrLf & _
"Would you like to view this document?", _
vbYesNo + vbQuestion)

If result = vbYes Then
'
' If Yes, make browser visible
'
ie.Visible = True
Else
'
' If no, quit
'
ie.Quit
End If

Set ie = Nothing
End Sub

 

[Guest]

from command bar
data -> import external dat -> new web quer
and follow wizzard. If you only require on value edit the query and tick the value you need

Kind regards,

 

[Jon Peltier]

In Excel 97 (which I'm stuck with here at work), this doesn't give much
joy. However, I could paste the long URL into the File > Open dialog and
pull up the web page. The page had a number of defined names when it was
rendered in Excel, so you could use these or simply the cell references
to help extract the data.

- Jon