I get the following error message when I try and run the code below.
Run-time error '1004':
Unable to open
http://ichart.yahoo.com/table.csv?s=...=2007&d=11&e=:
The Internet site reports that the item you requested could not be found. (HTTP/1.0 404)
This code worked perfectly all day Friday but when I tried to run it first thing this morning it keeps giving me this message.
Option Explicit
Sub MyGetData()
Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name
Dim LastRow As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationAutomatic
Sheets("Data").Cells.Clear
Set DataSheet = ActiveSheet
StartDate = Sheet13.Range("startDate").Value
EndDate = Sheet13.Range("endDate").Value
Symbol = Sheet13.Range("ticker").Value
Sheets("Data").Range("a1").CurrentRegion.ClearContents
qurl = "http://ichart.yahoo.com/table.csv?s=" & Symbol
qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
"&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Sheets("Data").Range("a1") & "&q=q&y=0&z=" & _
Symbol & "&x=.csv"
QueryQuote:
With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False
Sheets("Data").Columns("A:G").ColumnWidth = 12
LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count
Sheets("Data").Sort.SortFields.Add Key:=Range("A2:A" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets("Data").Sort
.SetRange Range("A1:G" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Thanks for any help!!!
Bookmarks