.csv problem?

[Lars R.]

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

 

[Lars R.]

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 <[email protected]>
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 <[email protected]>
Datum: Tue, 28 Mar 2006 02:21:02 -0800
Lokal: Di 28 Mrz. 2006 11:21
Betreff: RE: 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 <[email protected]>
Datum: Tue, 28 Mar 2006 03:56:03 -0800
Lokal: Di 28 Mrz. 2006 12:56
Betreff: RE: 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