obtaining values from a web page

  • Thread starter Thread starter Pete
  • Start date Start date
P

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
 
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,
 
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
 
Back
Top