Macro Ran in Excel 97 but in XP. What Needs Changing

[JB]

Hello,
I have inherited a macro that ran fine with Excel 97 & Excel 2000 but
does not run with Excel XP.
I get the following error message when I run the macro now.

Application-defined or object-defined error Run-time error ‘1004'

The error seems to be in .BackgroundQuery = True see section
of code below. How do I need to change this so it run in Excel XP?

Thanks
JBEsr


Range("C7").CurrentRegion.ClearContents
i = 7
qurl = "http://finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("C2")
Range("c1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" &
qurl, Destination:=Sheets("yahoo").Range("C7")) 'JBE

.BackgroundQuery = True <<<Error Here

.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

 

[Peter Benson]

It's not related specifically to Excel XP. In the last few days Yahoo has
changed the format of it's download file link. Many versions of
Excel/IE/Windows which used to work fine no longer work. The coding for you
web query should be exactly the same in all versions of Excel from Excel 97
up to 2003.

I have almost every combination of Excel/Windows and IE imaginable running
of different PCs. Suddenly all PCs with IE 6/Win 2000/Win XP/Win/Win2000
and Excel 2000, and Excel 2003 no longer work with this specific Yahoo web
query. (Historical prices from Yahoo are no problem: it's just the quotes).
HOWEVER my PC with Excel 97 running under Win 95 and IE 5.1 still works. I
actually think it's an IE issue rather than an Excel issue (Excel uses the
underlying IE technology for web queries) as when I browse to the Yahoo site
I can no longer download the CSV file for a quote by right clicking the link
and select 'save as'. It comes up with an error. It didn't used to. I get
this error now with IE 6.0 with the latest security patches. On the other
hand IE 5.1 works fine. Also another Win XP PC at a friend's place ruining
IE 6.0 still works....

So, it's definitly related to changes at Yahoo, but why it works on some
combinations of windows/IE/Excel is a complete mystery.

I suggest you test out the Yahoo link using IE and if it fails like I say it
does then send a mail to Yahoo via their finance help page. (I have done
this but haven't received an acknowledgment after 3 days).

PS: a good workaround is to put a country code in your Yahoo URL. eg
http://ca.finance.yahoo... , where ca is canada or any other Yahoo country
site code. It's actually only the US Yahoo site that has the problems. If
it works when you use a country code then you know for sure the problem is
at the Yahoo US site end. Unfortunatley that workaround only works for
now -- I guess it's quite possible Yahoo will bring their non-US sites into
line with their US site and thus they will stop working as well.