How Can I Download Data onto my excel sheet with QueryTables

[Kenji]

I am using this code to retrieve data, but it gives me an error on the
"Refresh" line.

Error - "Run-time error '1004': Method 'Refresh' of object "_QueryTable"
failed.

---------------------------
With Sheet1.QueryTables.Add(Connection:= _
"URL;" + dataWebsiteString, _
Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

 

[Jim Gordon MVP]

Hi Kenji,

What version of Excel? Your code worked fine on my copy of Excel 2001 which
has all of the updates installed.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Before posting a "new" topic please be sure to search Google Groups to see
if your question has already been answered.

 

[Kenji]

Hi Jim,
It worked on a very short data, but on a long text return (around 1000
rows of words).. it stops on the

".Refresh BackgroundQuery:=False"

Do you know what I can do to fix this?

Kenji

 

[Kenji]

http://dbforums.com/t922365.html

This is the same exact problem I'm getting.. but the file I'm importing
exists for sure.

Kenji

 

[Jim Gordon MVP]

Hi Kenji,

It occurred to me that there might be some limit set by the w3c on how long
URLs can be. It appears that there is no limit at all:
ftp://ftp.isi.edu/in-notes/rfc2616.txt
"The HTTP protocol does not place any a priori limit on the length of
a URI. Servers MUST be able to handle the URI of any resource they
serve, and SHOULD be able to handle URIs of unbounded length if they
provide GET-based forms that could generate such URIs. A server
SHOULD return 414 (Request-URI Too Long) status if a URI is longer
than the server can handle (see section 10.4.15).

Note: Servers ought to be cautious about depending on URI lengths
above 255 bytes, because some older client or proxy
implementations might not properly support these lengths."

Internet Explorer has "a maximum path length of 2,048 characters. This limit
applies to both POST and GET request URLs. "
http://support.microsoft.com/default.aspx?scid=kb;[LN];208427

I'm trying to find out whether or not Excel has the same limitation as IE.
If it does, then the problem is either that the server is not accepting the
long URLs or there's a bug in VBA. If I find out anything I will post again.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Before posting a "new" topic please be sure to search Google Groups to see
if your question has already been answered.


----------