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]

Also take a look at the symbol you are sending when it has an error 1004. See if there is something unusual or invalid perhaps about the symbol.

 

[chuckchuckit]

I reread your posts as this one has been puzzling me. I was going to say try putting two strings caputuring your Yahoo query code after and before your query to see if anything has changed in your Yahoo query string, but seems not the case now.

I too noticed some changes last week in Yahoo finance queries. I do many various ones and one of the ones I do all of a sudden speeded up considerably. Although it being very slow before was not a problem on this one, it did speed up so Yahoo changed something (my code is the same).

I have noticed this many times with Yahoo finance as they can change their query table numbers around and such, so now it is looking like something in your code is having conflicts of sorts with a Yahoo finance change perhaps on their end.

Hard to diagnose but try putting a "DoEvents" line before your query so your computer and Excel program does not have anything hanging in its calculations and processing before each query.

You could also try changing the order in your Yahoo query string which may be just enough to change whatever is randomly conflicting with any possible Yahoo Finance.

Let us know if you resolve this as it is a curious one.

Thanks.

 

[eblake]

I've run into something similiar when trying to create 60+ worksheets in a single workbook, would randomnly die with the same error, without seeing the rest of your code i'm going to throw out a guess that your creating a worksheet for each of the stocks?

MS gives some tips on how to fix it here:
http://support.microsoft.com/kb/210684