How to filter the QueryTable from a Web query

[Felix_Jiang]

I use a Web query to obtain a table from a Web page. Before I put the table
into a range in Excel, how do I filter some rows out (like only keep rows
with the column value greater than 0)? Thanks so much.

 

[joel]

The best way is to open an internet explorer application and extrac
only the data you want. I can do this if I had the URL. Here is
simple exabple.

Sub findStock()
StockName = "ibm"
Quant = GetStock(StockName)

End Sub

Function GetStock(ByVal StockName As String)

NoResults = "There are no"

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

URLLOOKUP = "http://finance.yahoo.com/lookup?s="
URL = URLLOOKUP & StockName
TimeOut = False
StartTime = Now
'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4 Or _
IE.Busy = True
DoEvents
CurrentTime = Now
If Second(CurrentTime - StartTime) > 30 Then
TimeOut = True
GetStock = -1
Exit Do
End If
Loop

If TimeOut = False Then
Set form = IE.document.getElementsByTagName("Form")

Set Results = IE.document.getElementById("yfi_sym_results")

If Left(Results.innertext, Len(NoResults)) = NoResults Then
GetStock = 0
Else
Set Quantity = IE.document.getElementById("yfi_sym_lookup")
Text = Quantity.innertext
'get number from parenthesis
Quant = Mid(Text, InStr(Text, "(") + 1)
Quant = Val(Quant)
GetStock = Quant
End If
End If

IE.Quit


End Functio

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.