.csv file Text to Columns

[Duane L Kibby]

Hi,
The stock data ".csv" file downloaded from the internet displays the seven
comma delimited items all in one column.

2.91,200730,2.63,3.34,3.00,2.91,3.05
22.70,362487,22.65,24.19,22.06,21.91,22.83
13.28,115617,12.42,15.90,13.93,13.10,14.00

The code segment: (excel 2000)

Set AllStockDataWorksheet = Workbooks(1).Worksheets(4)
Set AllStockDataResults = AllStockDataWorksheet.QueryTables _
.Add(Connection:="URL;http://finance.yahoo.com/d/quotes.csv?s=" &
Left ( SymbolString, Len(SymbolString) - 1) & "&f=l1va2c1&e=.csv", _
Destination:=AllStockDataWorksheet.Cells(RowNumber, 4))


With AllStockDataResults
.Refresh (BackgroundQuery)
End With

For some reason the data is not distrubuted across the columns, but is all
piles in one. I can do manually, but I need it to be programmatically.

Thanks Duane

 

[Bernie Deitrick]

Duane,

Try adding these lines to the end of your code:

With AllStockDataWorksheet
.Range(.Cells(rownumber, 4), .Cells(rownumber, 4).End(xlDown)) _
.TextToColumns DataType:=xlDelimited, Comma:=True
End With

HTH,
Bernie
MS Excel MVP