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!

 

[smithj30]

Worked for me on Vista Business OS & IE7

 

[s7pete]

Verrrrry interesting. Can you confirm that you executed an excel web query with an imbedded "?" in the script, including having the web page 'IMPORT' into a spreadsheet.

I know that entering such a URL in the browser will access the web page, even in Vista Business and IE7; but my limited testing (XP and IE7) shows it will fail to import to a web page; generating a bad file name error message. Maybe Vista overcomes the problem??

Thanks for your reply and special thanks if you can take the time to test it this one step further.

s7pete

 

[smithj30]

hmmm - that is strange. Here's the code I threw together:

Sub Macro1()
    With ActiveSheet.QueryTables(1)
        .Connection = "URL;http://finance.yahoo.com/q/hp?s=MSFT"
        .Refresh BackgroundQuery:=False
    End With
End Sub

I used the wizard for the initial setup of the web query. I confirmed that I could change the ticker in the URL and rerun the macro to pull in the new data and it worked fine.

 

[s7pete]

Thank you very much for trying that. That code is almost identical to mine that failed in my XP/IE7 system. Maybe its my pre-Office2007 version that is incompatible. Anyway, your help is one more piece of the puzzle.

 

[john rosenthal]

Download Open Office Calc. Check their help screen to find out how to do a web query in one of their spreadsheets. Its not as user friendly as Excel because you have to know the number of the table in Yahoo's page in order to pull it in, but it's not too hard. If you want Yahoo's table of historical prices it is number 20. If that's not what you want, use trial and error. Once you have it right, store the spreadsheet with the option of storing as an .XLS file. You can update your Excel workbook by linking to this "bootleg" .xls file. When you want to update your data, open the spreadsheet with Open Office. Then after the web update you can save it and close it (or you might be able to leave it open; I have not tried that yet) and then open the regular Excel workbook (using Excel) that is linked, and let Excel do its update by linking to the spreadsheet.

Or you may be able to de everything you want in Open Office, from beginning to end.

Let me know if you find this helpful and/or if you find a different solution.

 

[rookieExcelvba]

I'm having the exact same issue. It works fine and did so for about 30 days then that same ERROR message appeared. If I manually refresh it is OK and when try to excute from VBA again it will sometimes (more often than not) give the same erro. It took a while for it to appear.

I have gone in and deleteed any extra names but to no avail.

Any Suggestions as am new to VBA.

Thanks

 

[mole999]

would something like this work

Sub Macro1()
Dim Mytrap
Mytrap = ?
    With ActiveSheet.QueryTables(1)
        .Connection = "URL;http://finance.yahoo.com/q/hp" & "MyTrap" & "s=MSFT"
        .Refresh BackgroundQuery:=False
    End With
End Sub

i was trying it with Chars (63) just a idea

its suffering errors though

 

[john rosenthal]

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.

 

[rookieExcelvba]

Thanks. That seems to do the trick!