Home |
Search |
Today's Posts |
#1
|
|||
|
|||
.csv file Text to Columns
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 |
#2
|
|||
|
|||
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 "Duane L Kibby" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading a text file ? | Excel Worksheet Functions | |||
can i save an existing .xls file as a .csv file using command line | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Text To Columns | Excel Worksheet Functions | |||
How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions |