Dreaded 1004 error with looping web queries

bellawala

Board Regular
Joined
Aug 20, 2002
Messages
73
I realize that this problem has been described many times on this board, but I have not been able to find a solution that works for me. Basically, I'm trying to download web data from google. It works for a random number of times (usually 20+) then gives me the 1004 error. If I exit excel and empty the temporary files folder, I get it to run for another 20+ times.

I tried to delete the temp files after each run with:

Kill Environ("UserProfile") & "\Local Settings\Temporary Internet Files\*.*"

but that doesn't seem to do the trick.

My complete code is as follows:
For i = 2 To 200

' delete all querytables for current worksheet
While ThisWorkbook.Worksheets("Sheet1").QueryTables.Count > 0
ThisWorkbook.Worksheets("Sheet1").QueryTables(ThisWorkbook.Worksheets("FundBeta").QueryTables.Count).Delete
Wend
ThisWorkbook.Worksheets("Sheet1").Activate

With ThisWorkbook.Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://finance.google.com/finance?q=" & <symbol gets added here from table indexed by loop variable i>, Destination:=Range("A1"))
.Name = "finQuery"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Next i


Thanks for any help.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hey, Could be just me but some times I get that error if the macro uses too much excel memory.

Regards,
-Dude
 
Upvote 0
Check out this link it is Microsoft's answer which is not really an answer but can give you more info on the error
 
Upvote 0
Thanks, but that link didn't fix my problem. What I did do was to change my code from deleting and adding the web query to just updating it and it took about twice as many passes to get the error.

I'm doing a full page web query currently. My next test will be to run it downloading only specific sections to see if that changes anything.

Thanks for the help!
 
Upvote 0
I don't think it's a timeout issue, because once it happens, it will keep on happening until I exit excel and get back in and try again. If I delete temp files in the meantime, it goes much further.
 
Upvote 0
Well, what I'm saying is if you get a timeout you get HTTP error 404 (http://en.wikipedia.org/wiki/404_error), and that as soon as a query is refreshed and receives that error, Excel throws error 1004 but then for some reason Excel keeps throwing Error 1004 for all subsequent query refreshes until you close and restart Excel. So it's like Excel can't recover from HTTP 404.

If you're not getting a timeout, are you sure the data or page you're requesting exists?
 
Upvote 0
Basically, the program is looping through a bunch of stock information based on the symbols in a list in the spreadsheet. When I get 1004 error, I exit and restart with the offending symbol. It then works for a while until it dies.
 
Upvote 0
What version of Excel are you using ? I had a similar problem with version 2002 which was caused by "memory leakage".

I got around it by saving, closing and re-opening the file every 5 iterations of the loop.

May be a solution would be to have two workbooks. One would open the other - say on a command click event. Then, every 5 iterations, you save, close and re-open the second workbook. This is the approach that I used and it worked. Thanks

Regards

Kaps
 
Upvote 0

Forum statistics

Threads
1,215,375
Messages
6,124,578
Members
449,174
Latest member
chandan4057

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top