run time error 1004

[tlindeman]

I have lengthy code that essentialy connects to Yahoo and downloads the balance sheet, income statement, and statement of cash flows for stocks. It connects and removes the connections 50 times, sometimes it works, other times I get a run time error 1004. Please look at a sample of my code and suggest a more efficeint way to do this so I do not get the run time error.


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/bs?s=" & Range("A1").Value, Destination:=Range("$B$1"))
.Name = "bs?s=" & Range("A1").Value & "&ID_NOTATION=253929"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
ActiveWorkbook.Connections("connection").Delete
End With
If Range("A50").Value = "" Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/bs?s=" & Range("A50").Value, Destination:=Range("$B$50"))
.Name = "bs?s=" & Range("A50").Value & "&ID_NOTATION=253929"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
ActiveWorkbook.Connections("connection").Delete
End With
If Range("A99").Value = "" Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/bs?s=" & Range("A99").Value, Destination:=Range("$B$99"))
.Name = "bs?s=" & Range("A50").Value & "&ID_NOTATION=253929"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
ActiveWorkbook.Connections("connection").Delete
End With

 

[chuckchuckit]

I get an occassional run-time error 1004 when doing a similar continuous web querying similar to yours to Yahoo.

It looks like Andrew found the answer to a similar error for someone else by suggesting they "enable ActiveX controls". His suggestion is post #6 here:
http://www.mrexcel.com/forum/showthread.php?t=528059&highlight=error+1004

And here is the link he suggested:
http://office.microsoft.com/en-us/e...controls-in-office-documents-HA010031067.aspx

I haven't had a chance yet to see if this fixes my occassional run-time error 1004 during my many automated querys to Yahoo in one of my programs.

If you try it and it fixes your 1004 error let us know.

Chuck

 

[teclontz]

I've tried every ActiveEx setting configuration I could, and still cannot get my macro to download data from yahoo finance without the 1004 error. Are there any other solutions?

I've tried On Error Goto (line) to try to get past this, but that doesn't work either.

It worked until recently, and now won't work at all.

 

[chuckchuckit]

Welcome to the MrExcel board.

There is a bug in Microsoft that affects macros started with using Ctrl-Shift key combo. The bug is in using the "Shift" key when later trying to use Workbook.Open as macro stops there with the 1004 run-time error.

Try starting your macro from the the VBA editor and pushing F5 to start macro instead. If the error goes away, try removing the "Shift" key from your macro key combination.

It is a nasty intermittant error. I was a baffled by it until I ran across this article documenting the "Shift" key bug:

http://www.jkp-ads.com/Articles/WorkbookOpenBug.asp

 

[teclontz]

I was loading the macro from the menu, rather than using the shift key.


The macro starts, and runs partially, before getting the error. Sometimes I can get almost twenty stocks loaded before the error, but until Friday it could load 1100 stocks without a glitch.

I just need to suppress or circumvent the error if possible. I'd rather miss an occasional stock load than lose the entire macro.

 

[chuckchuckit]

When the macro errors with 1004, click on "debug" and see which line of code it errors on.

If you can get it to error multiple times with 1004 and always on the same line of code, that will help much on figuring what is causing it.

 

[teclontz]

This is the line of code:

Workbooks.Open Filename:= _
"http://ichart.finance.yahoo.com/table.csv?s=" & TCK1 & "&d=4&e=5&f=2031&g=d&a=0&b=2&c=1970&ignore=.csv"

TCK1 is a variable. Until Friday, I could download 1100 stocks without a hitch on multiple computers. After Friday, it will do anywhere from 1 to 30 before it errors out -- never on the same stock twice. It's totally random before it craps out.

The 2031 date forces the code to always go to the last available date. Again, it can do 1, 10, 20, 30 without a hitch before it just stops.

I need a way to suppress the error, since I'm trying to compare sectors and industries and can live without a random stock being populated. The bulk is what I need.

Tim

 

[chuckchuckit]

"Workbooks.Open" is exact same code line where I was getting the 1004 run-time error when launching a macro using "Shift" key combo. But since you are starting this macro from clicking on the menu instead, it must be something else. It is still possible you are clicking on the "Run" part of the menu that may be still invoking the "Shift" key inadvertantly.

Try again just opening the code, click your cursor anywhere inside the starting sub code for your macro (so your mouse is now active there), then push F5 to run the macro that way. This will avoid all possibilities of using the "Shift" key if you do not use the menu at all to start your macro. As I am still suspecting the "Shift" key possible problem here.

I would be very sure of the above first, before trying the next procedure below.

If you have your queries to Yahoo in a loop I would try:

On Error Resume Next [COLOR=seagreen]‘if an error, resume next line of code[/COLOR]
 
   [COLOR=seagreen]'Your looping code misc here for Yahoo data[/COLOR]
 
On Error GoTo 0 [COLOR=seagreen]'reset back to normal error handling[/COLOR]

Any error after the "On Error Resume Next" line, will simply ignor the error and proceed to do the next line of code, which may in fact cause other trouble if your code was expecting to process data that is not now there due to the ignored error. But macro should not stop now due to the error anyway.

You may want to have special checking where there should be data and if it is not there, don't do that part of the processing and try again with the next Yahoo query instead. That way it will not try to process data that was not received due to an error.

 

[teclontz]

I tried the F5 solution from within the code, and the result was the same.

On Error Resume Next doesn't suppress the dialog box either, but I'll try again. I'm hoping this is a temporary problem, but am afraid it isn't.

 

[chuckchuckit]

See if this helps with suppressing the dialog box, not sure if it will (changes are in red):<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

[COLOR=black][COLOR=black]On Error Resume Next [/COLOR][COLOR=seagreen]‘if an error, resume next line of code[/COLOR]
[COLOR=black][COLOR=red]Application.DisplayAlerts = False[/COLOR] [COLOR=seagreen]' Turn off msgs [/COLOR][/COLOR][COLOR=black]<o:p></o:p>[/COLOR]
 
[COLOR=seagreen] 'Your looping code misc here for Yahoo data[/COLOR]
 
[COLOR=black][COLOR=red]Application.DisplayAlerts = True[/COLOR] [COLOR=seagreen]' Turn back on msgs[/COLOR] [/COLOR]
[COLOR=black]On Error GoTo 0 [/COLOR][COLOR=seagreen]'reset back to normal error handling[/COLOR]
[/COLOR]