Excel 2000 Web query not returning all Table Data

  • Thread starter Thread starter cmaeb
  • Start date Start date
C

cmaeb

I believe there is an undocumented "special feature"(Bug) using the
Excel 2000 web query,
Please correct me if I am wrong,

The following query in IE works fine :

http://globefunddb.theglobeandmail....g_col=STD_fund+name&orig_order=ASC&result_cnt
17&fr_param11=&fr_param12=&fr_param13=&fr_param14=&pi_universe=IGF_FUNDS&product_id=

but in the following module-same query No Cigar! What's up?

sub get_it
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://globefunddb.theglobeandmail....eration=+&orig_col=STD_fund+name&orig_order=D
SC&result_cnt=17&fr_param11=&fr_param12=&fr_param13=&fr_param14=&pi_universe=IGF_FUNDS&product_id=",
_
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub
 
Try something like this:

sub get_it
With ActiveSheet.QueryTables.Add(Connection:= _
Array( _
"URL;http://globefunddb.theglobeandmail.com/gishome/plsql/igf.gen_fr?", _
"in_rep_type=STD&in_sort_col=STD_fund+name&in_orig_start=+&in_curr_val", _
"=+&in_orig_fund=+&in_curr_fund=+&in_direction=FWD&fr_param1=+&fr_param2=",
_
"+&fr_param3=+&fr_param4=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_param8=",
_
"+&fr_param9=+&fr_param10=+&fr_mode=MYFUNDLIST&msg=+&page_no=1&generation=+"
, _
"&orig_col=STD_fund+name&orig_order=DSC&result_cnt=17&fr_param11=&fr_param12
", _
"=&fr_param13=&fr_param14=&pi_universe=IGF_FUNDS&product_id="), _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub
 
Thanks for the Info Tim,

I tried this and it still only returns the first Fund in the Fund
List, see below,
any other suggestions would be greatly appreciated.

Fund name Price $ 1 day 30 day % 3 mo % 6 mo % 1 yr % 3 yr % Incep
(mm/yy) Charts
$ Chg
IG AGF International Equity-C
8.711 0.096 7.2 2.32 11.44 -3.22 -17.3 -1.39 (03/96) .


cheers
Colin
 
Best I can show you is how to break a long URL into sections in an array so
it will execute. You will have to write the correct URL.
 
Tom Ogilvy said:
Best I can show you is how to break a long URL into sections in an array so
it will execute. You will have to write the correct URL.
Click to expand...

Thanks Tom for the suggestion , But even doing this ... break the url
into an array ... still returns one fund. I think it must be Java
related - The only work around I see is to create a one web query for
each fund on each sheet then link all to a master sheet.
 
If the URL returns multiple funds when done manually, I would expect it to
do the same thing if executed in your web query. When I tested your manual
query previously, it appeared to need a local file that lists the funds to
return. But, I don't know anything about this site, so I can't say what
the problem is.
 
Back
Top