Run-time error '1004'

[ShaunC]

Hi all

I am trying to adapt an existing spreadsheet to obtain data from Yahoo Finance. The original spreadsheet works flawlessly as does my copy when the range from A7:AXXXX follows the format yyy (yyy being stock codes for the US exchange). However, I need to pull the data from the same site but from the Australian exchange. To do this all the stock codes become yyy.ax. The .ax signifies the Australian Stock Exchange (ASX).

The macro works when codes entered (in the yyy.ax) are in the range A7:A145, but when the ASX codes are entered from A146 onward I get the run time error:

Run-time error '1004':
An unexpected error has occurred.

When I click on the Debug button the following is highlighted in yellow:

.Refresh BackgroundQuery:=False

I have copied the code below:

Sub GetData()
Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer, iMax As Integer

Clear

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic

Set DataSheet = ActiveSheet


For iMax = 0 To 1000 Step 200

i = 7 + iMax
If Cells(i, 1) = "" Then
GoTo stopHere
End If

qurl = "removed link" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> "" And i < iMax + 207
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("B2")
Range("b1") = qurl

QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("N7"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Range("N7:N207").Select
Selection.TextToColumns Destination:=Range("N7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))

Range("N7:W207").Select
Selection.Copy
Cells(7 + iMax, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' Range("N7:W207").Select
' Selection.ClearContents
Next iMax
With ThisWorkbook
For Each nQuery In Names
If IsNumeric(Right(nQuery.Name, 1)) Then
nQuery.Delete
End If
Next nQuery
End With

'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
' Range("C7:H2000").Select
' Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' Columns("C:C").ColumnWidth = 25.43
' Range("h2").Select

stopHere:
Clear2
End Sub
Sub Clear()
'
' Clear Macro
'
'
Range("C7:L1200").Select
Selection.ClearContents

End Sub
Sub Clear2()
'
' clear2 Macro
'
'
Columns("N:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub
Sub doALL()
Sheets("Yahoo1").Select
GetData
Sheets("Yahoo2").Select
GetData
Sheets("Yahoo3").Select
GetData
End Sub
Sub move()
'
' move Macro
'
'
Range("K7:R207").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
Range("C7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Sub Txt_Col()
'
' Txt_Col Macro
'
'
Range("C7:C480").Select
Selection.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1))
End Sub


The original code can be found by searching for "Yahoo3.xls" and saving the file in the second link. A link for Gummy-Stuff (the original author) can be found with this search also.

Any assistance that can be provided would be greatly appreciated.
:beerchug:
Shaun

[p45cal]

A guess: Is your connection string to long as a single string? each oz symbol is quite long. If you record a macro ading a querytable, longer connection strings are an array of arrays, perhaps doing something similar n code might work?

[ShaunC]

A guess: Is your connection string to long as a single string? each oz symbol is quite long. If you record a macro ading a querytable, longer connection strings are an array of arrays, perhaps doing something similar n code might work?

I played with it further yesterday and I think I stumbled across a solution in line with what you are suggeesting.

I changed the line:
For iMax = 0 To 1000 Step 200
to:
For iMax = 0 To 2400 Step 100

I up'd it to 2400 because I am getting data for ~2300 stocks but lowered the step. I also changed:
While Cells(i, 1) <> "" And i < iMax + 207
to:
While Cells(i, 1) <> "" And i < iMax + 107
to keep everything in line.

The result was no error and the data updated beautifully. On my work computer it took 46 seconds to upate all data and on my home pc it took 15 seconds. This suggests to me Yahoo Finance has a lot of traffic and could not cope with the request and requesting 200 * 10 bits of data each time overwhelmed it. I ran the test on my home PC at about 9:00pm AEST when I would expect traffic to be low.

Thank you p45cal.

Now on to the next stage...

Cheers

Shaun