IE7 Prevents Excel/VBA Web Query with "?"

[s7pete]

Just registered to deal with this particular problem which I suspect will be shared by other Visual Basic developers. I saw the problem discussed on another forum, without a solution. I have only scanned the first several 'pages' of this forum so may be re-entering a problem discussed further in the past - if so, sorry.

I have developed an extensive application using excel and access data bases to manage investment (stock) data. My XP system (WAS planning to 'upgrade' to Vista) uses the VBA/excel web query function to extract data from selected web sites. In doing so the Office Apps use the installed version of Internet Explorer to conduct the web operations.

The connection URL typically will involve a complex scripted string:

http://finance.yahoo.com/q/hp?s=MSFT

this is a simple case that extracts historical prices (hp) for Microsoft (s= MSFT) from the Yahoo Finance web site. Note the embedded "?".

If one enters this URL in any browser (including IE7), it obtains the requested web page. However, when the IE7 browser operates 'under the covers' in an office application, it blocks importation of the web page into the application.

Backing up: With IE6 as the default browser one can, within an excel worksheet, query the above web page with <DATA> <NEW WEB QUERY> URL. Enter <GO> which brings up the web page in the imbedded IE6 browser. <IMPORT> downloads the page to the worksheet.

After dutifully updating to IE7 this is no longer possible. <GO> will find the web page (just like it will with the manual browser entry); but <IMPORT> will create an error messaged to the effect that the system cannot access the FILE; giving possible reasons of Read Only (not true), not in the system (Not True); and having invalid characters in the file name - citing amongst other "?". IE7 is apparently treating a web query under the same rules as local disc access.

Conducting the same test under IE7 using an URL without the "?" such as: http://finance.yahoo.com successfully imports. In fact, in my application, other web pages with very complex scripts albeit sans "?" import fine.

The error message makes it look like IE7 has introduced some 'Filename Nazi' that blocks valid use of an URL script with embedded "?" (other other characters of which I'm not aware). I surmise that the IE7 code developers, in their insulated manually operated browser environment (read IE7 for non-commerical applications) never considered or tested for Visual Basic operated Office applications that use IE7 'under the covers'.

Anyway, I had a 'crashed' XP system 'upgraded' with IE7. I did a system restore pre-IE7 and now MY system works again. However, as a commercial developer I'm screwed. My years of work (literally) cannot be run on new Vista (sold with IE7) or XP (upgraded to IE7) systems.

I know that there are many developers out there that use Office for data mining the web. I'm dismayed by the MSFT marketing decison to abandon those developers.

My questions:

1. Is my IE7 defect analysis correct -e.g. it is blocking web queries with its list of excluded characters intended for File Names which may or may not be applicable to web query URLs?

2. Is there a VBA work around that I can envoke in my Visual Basic Code that will shut down the MSFT FileName Nazi agent. Note: I've tried some 'On Error Resume Next' and similar strategies in VBA without success. Just for reference the error occurs at the 'Refresh' command:

With rYahooQ.QueryTable
.Connection = "URL;http://finance.yahoo.com/q/hp?s=MSFT"
.Refresh BackgroundQuery:=False
End With

where the range, "rYahooQ" has been set to a range with a previously initiated valid worksheet query. (works fine under IE6, not IE7)

3. Last resort: is there some IE7 patch?*

4. Last Last resort: Is there some way to install a stable browser (non-IE) into excel/access/VBA - one that won't be inadvertently 'upgraded' to a disfunctional state by potential users of my application?*

* obviously if I have to ask a potential customer to muck with his MSFT OS/IE7 to make an Office/VBA application run, I've severly limited the class of people who might buy such an application - hence 'last resort'

Thanks for your time and consideration. Looking forward to some salvation!

 

[john rosenthal]

Glad that helped. If you have in fact figured out how to embed the cleanup in VBA code, please share.

 

[s7pete]

Sorry for not having the solution at the end of this thread - I started another with the solution on about 14 Dec which I copy as follows:

tks to whoever put me on to the VBForum. I detailed the problem in a
previous post "Intro - problem with VB in Excel doing Web query from
Google". I turns out it is more generic than suggested in that
post.

The symptom is, after 50 or so web queries, a 'File Access Error'
message followed by an 'Invalid Web Query' message. This is false -
the site response and web query is fine, the fault lies with
temporary file and/or history cache overflows in IE7 that should not
be occuring.

Posts in the VBForum identified the InetCpl workaround. Here is my
report back to the VBForum.

"The problem was overflow in the IE7 temporary files and/or history
cache. I don't whether this defect is intrinsic to IE7 or was
introduced by an update or patch (somebody said it cropped up after a
Feb time frame MSFT update).

The workaround is to periodically purge the temp files and history
caches using the InetCpl.Cpl utility available to IE7. Something like:

If QueryCounter Mod 50 = 0 then
' whack temp files - Process 8
Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 8"
' whack history files - Process 1
Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 1"
End If

I don't know whether the temp files or history was overflowing but
its not relevant - once you're running InetCpl it's low overhead to
whack them both.

I run this initially and then every 50 stocks, followed by a
worksheet save. The save is good anyway but also allows the InetCpl
program to finish (it was reported this takes about 2 sec). I don't
know if InetCpl not finishing would cause a problem (I suspect not).

I still don't know why the Web queries would hang up for one site
(google) while still allowing Web queries for another site to
process. I also don't know what characteristic of the import page
causes the rapid (vs slow) overflow.

re 50 queries: I eyeballed the number of queries causing 'hang'
without purging to be about 70 queries (to two sites). After invoking
InetCpl every 50 stocks I was able to get through my processing run
of 3,000 stocks without error - I call that RESOLVED.

In the future I plan to detect the 'File Access' error code and just
do the cleanup when needed. For now, I'm back in business.

Thanks to dmaruca for putting me on to the solution and overall
advice.

I'll hang around here to contribute if I can be useful. For now, I'll
return to the xltrader yahoo group and report this problem/resolution.

Great Forum, tks, s7pete"

Hope this relatively simple workaround will save some traders some
grief.

BTW: I had another completely different web query program that gets craigslist listing for the entire country that worked perfectly before IE7 and updates; subsequently crashed, and then was corrected with the about workaround. Clearly its a bug in IE7 and/or Updates to same.

s7pete

 

[lorecam]

Hi all.. Im new in this forum... I also hava a huge problem trying to do a web query from Excel 2007.... same as yours...
Web query is from Google News, and I must get the number of news which google finds for some names:
For example, Microsoft.

I need to get about 150 names in this way, but randomly, when temporary files created from this web query reaches a random number, it shows that 'Invalid Web Query' error.

So i´ve tried to kill that temporary files that web query in excel creates, but no success. Even making this:
Kill "C:\Documents and Settings\User\Local Config\Internet Temporary Files\*.html"
does not work.. I get "file not found error"

So I also need a solution to this...
May I use this code from yours:? And how do I use it ?? in VB from Excel as a Macro?

If QueryCounter Mod 50 = 0 then
' whack temp files - Process 8
Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 8"
' whack history files - Process 1
Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 1"
End If

If I use it... all temporary files will be deleted... and history files too??
I don´t want to delete all those files.
I have found that the file created from that web query is called in cache as : "news[1].htm" ... but this name is a cache name... So I don´t know how to delete those files.
I think solution is to delete those specific files, But I can´t find a way to do it by VB...

Some ideas??
Thanks for your help... and good work !!

 

[s7pete]

Hi,
I really can't help with how to selectively delete temp and history files. In fact, its not obvious that this would work. The problem may be deeper and reside in some IE7/opsys cache you can't access with the higher level 'kill' method..

Using the InetCpl utility for IE7 simply empties the temporary and history file caches. This never used to be an issue since the browser only kept recent additions. Apparently the updates from the boyz in seattle screws up the cache overflow processing such that our previously working programs now crash with the erroroneous error message.

The code snippet I posted is just inserted in the VB Excel procedure code that does the web querying. On each web query I increment the variable 'QueryCounter' and, as shown, every 50 queries purge the temp and history files. I can't imagine what you might be doing that you would want to save some temp and history files.

good luck.

s7pete

 

[tcpgc]

The error message has wasted us a lot of time, sent us down blind alleys. The real problem, I have found, has nothing to do with the characters used in the query (explaining why such a query works sometimes but not others). The problem has to do with IE7's and/or Excel's sloppy use of temporary files. A temp file is used for each query. If you do more than about 30 at a crack (the exact limit varies somehow) further tries will fail and the bogus message is shown. If you run a shareware utility to clean up unused closed temp files and then try the query again it will work.

I just wish someone would make a VBA patch that can be added to an Excel spreadsheet to do the housekeeping required on Excel's behalf each time a query is completed. Then the work wouldn't need manual intervention.

Which shareware utility?
Can it be set so that I can run as many queries as I like?

 

[s7pete]

There are a number of shareware utilities for purging temp (and history) files - one I tried let you run it from VGA code. Of hand, I don't remember the 'brand' but I was advised to 'keep it simple' and run the IE7 utility that comes with IE7:

Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 8"

The InetCpl utility clears temp files by Process 8 and History by Process 1. Its really much easier than running a shareware program.

 

[lorecam]

Thank you very much s7pete for your help.

I inserted that code in my macro and it worked very well. No error found since then.
I glad a lot your help !!!
You are a nice person

I had a bit of fear about killing all temp files and history files beocuse of loosing temp files while IE is open. And also about loosing history of web pages visited, but i think these other things are not affected, are they?
Thanks again!!

Hi,
I really can't help with how to selectively delete temp and history files. In fact, its not obvious that this would work. The problem may be deeper and reside in some IE7/opsys cache you can't access with the higher level 'kill' method..

Using the InetCpl utility for IE7 simply empties the temporary and history file caches. This never used to be an issue since the browser only kept recent additions. Apparently the updates from the boyz in seattle screws up the cache overflow processing such that our previously working programs now crash with the erroroneous error message.

The code snippet I posted is just inserted in the VB Excel procedure code that does the web querying. On each web query I increment the variable 'QueryCounter' and, as shown, every 50 queries purge the temp and history files. I can't imagine what you might be doing that you would want to save some temp and history files.

good luck.

s7pete