Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.csv problem?
hallo,
i found the following macro on the web, that helps me get stock prices from yahoo into a spreadsheet, but i have a problem with the seperation of the cells. Sub GetYQuotes() Base01 = "http://de.finance.yahoo.com/d/quotes.csv?s=" '("http:// finance.yahoo.com/d/quotes.csv?s=") Base02 = "&f=sl1d1t1c1ohgv&e=.csv" surl = "" SymString = "" LastRow = Cells(65536, 1).End(xlUp).Row For i = 1 To LastRow SymString = SymString & Cells(i, 1) & " " Next i surl = Base01 & SymString & Base02 Workbooks.Open surl Set rngSource = Cells(1).CurrentRegion x = rngSource.Rows.Count y = rngSource.Columns.Count With ThisWorkbook.Sheets(1) Set rngDest = Range(.Cells(1, 1), .Cells(x, y)) End With rngDest.Value = rngSource.Value ActiveWorkbook.Close SaveChanges:=False End Sub the problem is, that if i run it with german yahoo finance site, the information about the stock will be seperated wrongly ("DCX.DE;48" "52;2/2/2007;17:35;+0" "28;48" "02;48" "64;47" "87;5770938") The correct separation would be ("DCX.DE" "48,52" "2/2/2007" "17:35" "+0,28" "48,02"; "48,64" "47,87" "5770938). Seems to be a problem with vba (perhaps a problem between english and german excel 2003 version with comma and semicolon). If i introduce the web address in the browser, excel opens a file with correct seperations. ("http://de.finance.yahoo.com/d/quotes.csv?s=DCX.DE &f=sl1d1t1c1ohgv&e=.csv") I tried to modify the workbooks.open method (format and delimiter) but it didn't work. Any idea? Thank you very much Lars |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.csv problem?
On 2 Feb., 21:26, "Lars R." wrote:
hallo, i found the following macro on the web, that helps me get stock prices from yahoo into a spreadsheet, but i have aproblemwith the seperation of the cells. Sub GetYQuotes() Base01 = "http://de.finance.yahoo.com/d/quotes.csv?s=" '("http:// finance.yahoo.com/d/quotes.csv?s=") Base02 = "&f=sl1d1t1c1ohgv&e=.csv" surl = "" SymString = "" LastRow = Cells(65536, 1).End(xlUp).Row For i = 1 To LastRow SymString = SymString & Cells(i, 1) & " " Next i surl = Base01 & SymString & Base02 Workbooks.Open surl Set rngSource = Cells(1).CurrentRegion x = rngSource.Rows.Count y = rngSource.Columns.Count With ThisWorkbook.Sheets(1) Set rngDest = Range(.Cells(1, 1), .Cells(x, y)) End With rngDest.Value = rngSource.Value ActiveWorkbook.Close SaveChanges:=False End Sub theproblemis, that if i run it with german yahoo finance site, the information about the stock will be seperated wrongly ("DCX.DE;48" "52;2/2/2007;17:35;+0" "28;48" "02;48" "64;47" "87;5770938") The correct separation would be ("DCX.DE" "48,52" "2/2/2007" "17:35" "+0,28" "48,02"; "48,64" "47,87" "5770938). Seems to be aproblemwith vba (perhaps aproblembetween english and german excel 2003 version with comma and semicolon). If i introduce the web address in the browser, excel opens a file with correct seperations. ("http://de.finance.yahoo.com/d/quotes.csv?s=DCX.DE &f=sl1d1t1c1ohgv&e=.csv") I tried to modify the workbooks.open method (format and delimiter) but it didn't work. Any idea? Thank you very much Lars finally i found the solution in this newsgroup (sorry for posting), but the question about the incompatibility between excel 2000 und excel 2003 still obtain. 8 Mrz. 2006, 10:58 Newsgroups: microsoft.public.excel.programming Von: Stefi Datum: Tue, 28 Mar 2006 01:58:02 -0800 Lokal: Di 28 Mrz. 2006 10:58 Betreff: XL2003 open .CSV problem Hi All, Earlier I used XL2000, and I had a macro opening a .CSV file: Workbooks.Open Filename:="C:\Myfolder\2005_12.csv" worked correctly, it placed the semicolon-separated data into different columns (I use a Hungarian version where list separator is semicolon). Recently I had to upgrade to XL2003 (because I couldn't go without some feature existing only in XL2003), and this statement doesn't work correctly in XL2003: it opens the .csv file, but does NOT place the semicolon- separated data into different columns. I tried Workbooks.Open Filename:="C:\Myfolder\2005_12.csv", Format:=4 Workbooks.Open Filename:="C:\Myfolder\2005_12.csv", Format:=6, delimiter:=";" with the same result. Can anybody help? Regards, Stefi 28 Mrz. 2006, 11:21 Newsgroups: microsoft.public.excel.programming Von: Martin Datum: Tue, 28 Mar 2006 02:21:02 -0800 Lokal: Di 28 Mrz. 2006 11:21 Betreff: XL2003 open .CSV problem Antwort an Autor | Weiterleiten | Drucken | Einzelne Nachricht | Original anzeigen | Diese Nachricht melden | Nachrichten dieses Autors suchen How about: Workbooks.OpenText Filename:="C:\Myfolder\2005_12.csv", DataType:=xlDelimited, Semicolon:=True (I just got this by recording a macro, opening the file, using the import wizard with a semicolon and then cutting out the needless arguments) 28 Mrz. 2006, 12:56 Newsgroups: microsoft.public.excel.programming Von: Stefi Datum: Tue, 28 Mar 2006 03:56:03 -0800 Lokal: Di 28 Mrz. 2006 12:56 Betreff: XL2003 open .CSV problem Antwort an Autor | Weiterleiten | Drucken | Einzelne Nachricht | Original anzeigen | Diese Nachricht melden | Nachrichten dieses Autors suchen Thanks Martin, Your idea put me on the right track, but the Opentext method worked only when I changed the file extension from .csv to .txt! My questions still obtain: why is not XL2003 compatible with XL2000? Regards, Stefi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |