Excel 2000 Web query not returning all Table Data
 

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.c...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.c...e&orig_order=D
SC&result_cnt=17&fr_param11=&fr_param12=&fr_param1 3=&fr_param14=&pi_universe=IGF_FUNDS&product_id=" ,
_
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub

Excel 2000 Web query not returning all Table Data
 

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&p roduct_id="), _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub



--
Regards,
Tom Ogilvy

wrote in message
om...
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.c...r?in_rep_type=
STD&in_sort_col=STD_fund+name&in_orig_start=+&in_c urr_val=+&in_orig_fund=+&i
n_curr_fund=+&in_direction=FWD&fr_param1=+&fr_para m2=+&fr_param3=+&fr_param4
=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_param8=+ &fr_param9=+&fr_param10=+&
fr_mode=MYFUNDLIST&msg=+&page_no=1&generation=+&or ig_col=STD_fund+name&orig_
order=ASC&result_cnt

17&fr_param11=&fr_param12=&fr_param13=&fr_param14= &pi_universe=IGF_FUNDS&pro
duct_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.c...gen_fr?in_rep_
type=STD&in_sort_col=STD_fund+name&in_orig_start=+ &in_curr_val=+&in_orig_fun
d=+&in_curr_fund=+&in_direction=FWD&fr_param1=+&fr _param2=+&fr_param3=+&fr_p
aram4=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_par am8=+&fr_param9=+&fr_param
10=+&fr_mode=MYFUNDLIST&msg=+&page_no=1&generation =+&orig_col=STD_fund+name&
orig_order=D

SC&result_cnt=17&fr_param11=&fr_param12=&fr_param1 3=&fr_param14=&pi_universe
=IGF_FUNDS&product_id=",
_
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub

Excel 2000 Web query not returning all Table Data
 

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




"Tom Ogilvy" wrote in message ...
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&p roduct_id="), _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub



--
Regards,
Tom Ogilvy

wrote in message
om...
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.c...r?in_rep_type=
STD&in_sort_col=STD_fund+name&in_orig_start=+&in_c urr_val=+&in_orig_fund=+&i
n_curr_fund=+&in_direction=FWD&fr_param1=+&fr_para m2=+&fr_param3=+&fr_param4
=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_param8=+ &fr_param9=+&fr_param10=+&
fr_mode=MYFUNDLIST&msg=+&page_no=1&generation=+&or ig_col=STD_fund+name&orig_
order=ASC&result_cnt

17&fr_param11=&fr_param12=&fr_param13=&fr_param14= &pi_universe=IGF_FUNDS&pro
duct_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.c...gen_fr?in_rep_
type=STD&in_sort_col=STD_fund+name&in_orig_start=+ &in_curr_val=+&in_orig_fun
d=+&in_curr_fund=+&in_direction=FWD&fr_param1=+&fr _param2=+&fr_param3=+&fr_p
aram4=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_par am8=+&fr_param9=+&fr_param
10=+&fr_mode=MYFUNDLIST&msg=+&page_no=1&generation =+&orig_col=STD_fund+name&
orig_order=D

SC&result_cnt=17&fr_param11=&fr_param12=&fr_param1 3=&fr_param14=&pi_universe
=IGF_FUNDS&product_id=",
_
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub

Excel 2000 Web query not returning all Table Data
 

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.

--
Regards,
Tom Ogilvy

wrote in message
om...
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




"Tom Ogilvy" wrote in message
...
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&p roduct_id="), _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub



--
Regards,
Tom Ogilvy

wrote in message
om...
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.c...r?in_rep_type=

STD&in_sort_col=STD_fund+name&in_orig_start=+&in_c urr_val=+&in_orig_fund=+&i

n_curr_fund=+&in_direction=FWD&fr_param1=+&fr_para m2=+&fr_param3=+&fr_param4

=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_param8=+ &fr_param9=+&fr_param10=+&

fr_mode=MYFUNDLIST&msg=+&page_no=1&generation=+&or ig_col=STD_fund+name&orig_
order=ASC&result_cnt


17&fr_param11=&fr_param12=&fr_param13=&fr_param14= &pi_universe=IGF_FUNDS&pro
duct_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.c...gen_fr?in_rep_

type=STD&in_sort_col=STD_fund+name&in_orig_start=+ &in_curr_val=+&in_orig_fun

d=+&in_curr_fund=+&in_direction=FWD&fr_param1=+&fr _param2=+&fr_param3=+&fr_p

aram4=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_par am8=+&fr_param9=+&fr_param

10=+&fr_mode=MYFUNDLIST&msg=+&page_no=1&generation =+&orig_col=STD_fund+name&
orig_order=D


SC&result_cnt=17&fr_param11=&fr_param12=&fr_param1 3=&fr_param14=&pi_universe
=IGF_FUNDS&product_id=",
_
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub

Excel 2000 Web query not returning all Table Data
 

"Tom Ogilvy" wrote in message ...
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.

--
Regards,
Tom Ogilvy

wrote in message
om...
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




"Tom Ogilvy" wrote in message
...
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&p roduct_id="), _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub



--
Regards,
Tom Ogilvy

wrote in message
om...
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.c...r?in_rep_type=

STD&in_sort_col=STD_fund+name&in_orig_start=+&in_c urr_val=+&in_orig_fund=+&i

n_curr_fund=+&in_direction=FWD&fr_param1=+&fr_para m2=+&fr_param3=+&fr_param4

=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_param8=+ &fr_param9=+&fr_param10=+&

fr_mode=MYFUNDLIST&msg=+&page_no=1&generation=+&or ig_col=STD_fund+name&orig_
order=ASC&result_cnt


17&fr_param11=&fr_param12=&fr_param13=&fr_param14= &pi_universe=IGF_FUNDS&pro
duct_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.c...gen_fr?in_rep_

type=STD&in_sort_col=STD_fund+name&in_orig_start=+ &in_curr_val=+&in_orig_fun

d=+&in_curr_fund=+&in_direction=FWD&fr_param1=+&fr _param2=+&fr_param3=+&fr_p

aram4=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_par am8=+&fr_param9=+&fr_param

10=+&fr_mode=MYFUNDLIST&msg=+&page_no=1&generation =+&orig_col=STD_fund+name&
orig_order=D


SC&result_cnt=17&fr_param11=&fr_param12=&fr_param1 3=&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 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.

Excel 2000 Web query not returning all Table Data
 

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.

--
Regards,
Tom Ogilvy

wrote in message
om...
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

wrote in message
om...
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




"Tom Ogilvy" wrote in message
...
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&p roduct_id="), _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
end sub



--
Regards,
Tom Ogilvy

wrote in message
om...
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.c...r?in_rep_type=


STD&in_sort_col=STD_fund+name&in_orig_start=+&in_c urr_val=+&in_orig_fund=+&i


n_curr_fund=+&in_direction=FWD&fr_param1=+&fr_para m2=+&fr_param3=+&fr_param4


=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_param8=+ &fr_param9=+&fr_param10=+&


fr_mode=MYFUNDLIST&msg=+&page_no=1&generation=+&or ig_col=STD_fund+name&orig_
order=ASC&result_cnt



17&fr_param11=&fr_param12=&fr_param13=&fr_param14= &pi_universe=IGF_FUNDS&pro
duct_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.c...gen_fr?in_rep_


type=STD&in_sort_col=STD_fund+name&in_orig_start=+ &in_curr_val=+&in_orig_fun


d=+&in_curr_fund=+&in_direction=FWD&fr_param1=+&fr _param2=+&fr_param3=+&fr_p


aram4=+&fr_param5=+&fr_param6=+&fr_param7=+&fr_par am8=+&fr_param9=+&fr_param


10=+&fr_mode=MYFUNDLIST&msg=+&page_no=1&generation =+&orig_col=STD_fund+name&
orig_order=D



SC&result_cnt=17&fr_param11=&fr_param12=&fr_param1 3=&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 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.