Sort CSV file from Yahoo Finance Query

[Jon]

I got the following bit of code on this discussion forum(which I have
modified slightly). It is supposed to pull the historical stock prices off of
yahoo finance. The problem is that it imports entire rows of .csv info into a
single cell. How do I sort the data into columns? I have tried the
textfilecommadelimiter property with no success. Thanks a lot.

Jon


'Retrieve Variables to place inside a URL String
Sub getVars()
'Declare Variables for URL
Dim myURL As String
Dim Ticker As String
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim e As String
Dim f As String
Dim g As String

'Assign values from Cells to variables
Ticker = Cells(1, 1).Value
a = Cells(2, 1).Value
Cells(2, 1).Select
b = Cells(3, 1).Value
c = Cells(4, 1).Value
d = Cells(5, 1).Value
e = Cells(6, 1).Value
f = Cells(7, 1).Value
g = Cells(8, 1).Value

'Append variables of URL to one string myURL
myURL = "http://table.finance.yahoo.com/table.csv?s="
myURL = myURL + Ticker + "&a="
myURL = myURL + a + "&b="
myURL = myURL + b + "&c="
myURL = myURL + c + "&d="
myURL = myURL + d + "&e="
myURL = myURL + e + "&f="
myURL = myURL + f + "&g="
myURL = myURL + g

'Test Display string myURL in a cell
'Cells(15, 1).Value = myURL

'Some code i copied that will display the WHOLE HTML page in sheet 2
Dim WebCopy As Object
Set WebCopy = Sheets("Sheet2")

WebCopy.Cells.Clear

With WebCopy.QueryTables.Add(Connection:="URL;" & myURL,
Destination:=WebCopy.Range("A1"))
..BackgroundQuery = True
..TablesOnlyFromHTML = True
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True

..Refresh BackgroundQuery:=False
..SaveData = True
End With

'With Application
'.ScreenUpdating = True
'.DisplayAlerts = True
'.Calculation = xlCalculationAutomatic
'.Goto Range("A1"), True
'End With

End Sub

 

[Gary Keramidas]

did you experiment with the text to columns menu selection? if you get that
to work, you can record a macro while you do it. you need to know how the
file is delimited.