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:
this is a simple case that extracts
historical prices (hp) for Microsoft (s= MSFT) from the Yahoo Finance web site. Note the embedded "?".
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.
up: With IE6 as the default browser one can, within an excel worksheet, query the above web page with URL. Enter which
brings up the web page in the imbedded IE6 browser. downloads the page to the worksheet.
After dutifully updating
to IE7 this is no longer possible. will find the web page (just like it will with the manual browser entry); but 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
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.
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:
.Connection = "URL;http://finance.yahoo.com/q/hp?s=MSFT"
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!