Need help with Excel web query

Have you tried to refresh your stock quotes without deleting all the subfolders? Maybe what you have so far is good enough to make your macro work.

vbNormal should clear the ReadOnly attribute as well.

Hi All,
I've spent nearly 400 hours building a stock market evalation program in Excel '97 and VBA. The user writes calculation rules and Buy/Sell rules to evaluate when to buy and sell stocks.

I've included a 23 page PDF step by step.

http://www.geocities.com/bolin_chuck/index.html

I did not explain all of the features. I've have a much more updated version. I'd appreciate any feedback at [email protected].

I've started writing a specification to convert this concept program into a VB application.

Regards,
Chuck

I think I have solve the problem (I hope)...IE save the webpage under "q[*.htm", but there is a limit of how many such files can be saved (after some patch back in feb). when that limit is reached, excel can't access the webpage anymore and give a runtime error 1004. deleting these "q[*.htm" will clear up the space for excel to continue to access webpages.

I have written a two step solution:
first - to acquire the pathname of the temporary internet files folder from windows registry
second - to delete these "q[*.htm" files

Note: operating system files must be set to be visible, set it in folder option.
SetAttr to vbNormal is not going to work for the temporary internet files folder.

Hope that helps.




' Step 1
' Obtain the path name of the temporary internet files folder from windows registry
' Tested for IE version 7.0.5730.11 on Windows XP SP2 (both home and pro editions)
Dim oWSH As Object
Dim TempIntFilePath As String
Dim RegKey As String
Dim FileTypeDelete As String
Dim c As Integer
Dim answer

RegKey = "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders\Cache"

If oWSH Is Nothing Then
Set oWSH = CreateObject("WScript.Shell")
End If

TempIntFilePath = oWSH.RegRead(RegKey) & "\Content.IE5"




' Step 2
' Delete yahoo "q[*.htm" files from the temporary internet files folder
' Note 1: Make sure operating system files is visible, otherwise will not work
' From any explorer window -> Tools -> Folder Options... -> View ->
' (Hide protected operating system files) uncheck
' Note 2: Delete all Temporary Internet Files before running this macro will improve speed
' Internet Properties -> General -> (Browsing history) Delete... ->
' (Temporary Internet Files) Delete files...
With Application.FileSearch
.LookIn = TempIntFilePath
.SearchSubFolders = True
.Filename = "q[*.htm"
.MatchTextExactly = True
If .Execute > 0 Then
For c = 1 To .FoundFiles.Count
Kill (.FoundFiles(c))
Next c
Else
MsgBox ("Error: no file found. Make sure system files is visible")
Exit Sub
End If
End With

hi guys-
i have had the same problem when querying tracking numbers from freight companies' web sites. after 50 queries it 'stopped' pulling info. what i did is downloaded the freeware application called ccleaner that erases the temporary files for you.
In Excel vba i placed this code to run every 50 web queries were made to clear the temorary files:
Call Shell("C:\Reports\ccleaner.bat", vbHide)
it opens batch file where the ccleaner is run. everything takes approx. 2 sec to clean and the bat file looks like:
cd C:\Program Files\CCleaner\
CCleaner.exe /AUTO
EXIT
the ccleaner has inbuilt option /AUTO that lets run the application out from cmd line. that's really cool.
after Excel runs the ccleaner and clears the temp files, the next batch of 50 web queries runs smoothly.
Dorota

I have been having the same problem and none of the suggestions worked to delete all the temporary internet files except dbucz solution.

The ccleaner did work. My macro still gives the runtime error 1004. but now i dont have to manually go clean the temp files. i just re-initiate the macro, ie, just hit the run button again. I have in my code to start from the last terminated position so no problem.

Although, I do wish even this problem to go away. Any clever suggestions might be helpful. I will post if I come up with something.

Thanks dbucz.

I faced similar problem after moving from XP to Vista and updating the system with a few 'Windows Updates'.
The Excel Web Query to Yahoo! finance which used to work flawless on XP, started working sporadically. While going through this thread I realized that IE's setting is culprit.

By default, IE setting for page refresh is 'Automatically'.
(Tool -> Internet Options -> General tab -> Browsing History -> Settings -> Temporary Internet Files)
I changed it to 'Every time I visit the webpage' and cleared the temporary internet files and the same excel query without any change started working.

HTH.

Hello,

i was able to overcome this problem in phases

phase 1

1 found this program on microsoft website
How To Use a 32-Bit Application to Determine When a Shelled Process Ends
http://support.microsoft.com/kb/129796
2. kept track on my queries and after a magic number of 30+ was reached i would call the ccleaner to clean my temp file

phase 2
found another poster who had sample code to download historical quotes from yahoo website using
Set occXMLHTTP = CreateObject("Microsoft.XMLHTTP")
apparently if you query a webpage using this method
you do not lauch IE in the background and that makes it very fast to download your information.

phase 3
found a yahoo group that has a file called
rch_stock_market_functions.xla
download this file, and adopt this in your code,
no need to run ccleaner

MAN THIS IS THE FASTEST WAY ON EARTH TO DOWNLOAD DATA FROM ANY WEBPAGE!!!!

hope this helps!