Excel webquery questions

  • Thread starter Thread starter GF
  • Start date Start date
G

GF

I have about 500 URL's, which I want to use in webqueries.
I have two problems.

PROBLEM 1
^^^^^^^^^

Every URL returns a page with tables, and I know which table I need (for
every page the same table)

As an example, this is one of the URLS's.

http://www.realestate.com.au/cgi-bi...nlandsize=&minbed=4&maxbed=&parking=&p=10&o=p

I need the cel, displaying the first offer.
This webquery works, and returns:

----------

GREENFIELDSThe Real Mccoy
$195,000
4 bedroom House
Located in the suburb of Greenfields. Close to the future train station, the
bus station and the Greenfields Shopping center. This home has a lot of
promises. It features four bedrooms, two bathrooms, lounge and family
area...
New ReleaseStreet Map property details>>

----------


I only need the cell displaying the price $ 195,000
Is it possible, to import only that value to a certain cel in my excelsheet,
not the rest?
The value I need appears in B2, when I let the query run in A1.

I have 600 queries, from which I need to get only that first value (in this
case $ 195,000) in a column.



PROBLEM 2
^^^^^^^^^

I have a list of all the (600) URL's I need for all my queries, is it
possible to import those in one go, or is it possible to automaticly create
all the queries, when I provide the list of URL's?

Thanks for your help,
G.
 
I did not find that the url returned the same one at the top as you show...
But, you import and then use a macro or a formula using INDIRECT to fetch
the value needed to another sheet. You can use FIND within a macro.

You shouldn't need 500 separate urls. Just a list of the common denominators
and a macro to loop through the list>get the data> and then get the next one
to go next to the one on your list.
 
Don said:
I did not find that the url returned the same one at the top as you
show... But, you import and then use a macro or a formula using
INDIRECT to fetch the value needed to another sheet. You can use FIND
within a macro.

You shouldn't need 500 separate urls. Just a list of the common
denominators and a macro to loop through the list>get the data> and
then get the next one to go next to the one on your list.
Click to expand...

(I see the page has been updated, that's why I need that query :-)
The table I need now is containing the following information:

Spotlight On Value
Under Contract / Under Offer
$175,000
20 GAVARNIE WAY, COODANUP 4 bedroom House
Sack the Landlord ! Come and look at this 4 bedroom x 2 bathroom family
home. Open plan living area, kitchen/dining/family. Patio at the rear with
plenty of room for gardens and kids to play.

from which I need the $175,000 value

How would I create that loop you are talking about?
I have all the URL's, that is no problem.
But I have no clue on how to create a loop in Excel?
Can someone explain me?

When you say I need to get the data via INDIRECT and FIND on another sheet,
do you mean it is not possible to do it without that extra intermediate
sheet?

G.
 
This is a project that it generally outside the scope of the ng which is to
answer questions and not do projects which is how many of us make our
living.

Record a macro while editing your query and you get. Now you create a

for each var in range("a2:a500")
x= yourvariable within the url
..connection= _
"URL;http:// etc"& x & "rest of url
then do a copy to the main sheet where you want the data
next var

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/18/2005 by Don Guillett
'

'
Range("A1").Select
With Selection.QueryTable
.Connection = _

"URL;http://www.realestate.com.au/cgi-bin/rsearch?a=s&cu=&s=wa&ag=&t=res&snf
=ras&pm=&tb=&u=FURNISSDALE&px=&cat=&minlandsize=&minbed=4&maxbed=&parking=&p
=10&o=p"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "16"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
 
Back
Top