CODEWITHSUNDEEP
macosexcelvba
pdoak
pdoak

Reputation: 731

Mac querytables

I have written a script which downloads data from yahoo finance into excel using querytable. It should loop through each URL and download the data but it can't get past the second loop and fails at the .Refresh BackroundQuery:=False with the error code 1004 - An unexpected error has occurred.

Here is the code:

rowOffset = 0
url = Worksheets("Yahoo codes").Range("b2").Offset(rowOffset, 0)

Do While url <> ""

With ActiveSheet.QueryTables.Add(Connection:="URL;" & url, Destination:=Worksheets("Yahoo Data").Range("A65536").End(xlUp).Offset(1, 0))
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .BackgroundQuery = True
    .Refresh BackgroundQuery:=False


    '.Refresh

End With

rowOffset = rowOffset + 1
url = Worksheets("Yahoo Data").Range("a2").Offset(rowOffset, 0)
Loop

Upvotes: 0

Views: 1453

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

It looks like you get the first URL from B2 and subsequent URLs from A3...

When you get the error, got to the Immediate Window (Ctl+G) and type

?Worksheets("Yahoo Data").QueryTables(2).Connection

and see if it looks right. If not, I suspect your second url = ... statement is wrong.

Upvotes: 1

Related Questions