Comma-delimited files and Reional Settings

L

Lester

I am using Excel to download and process data from Yahoo, f.ex.

The code I use is
.....
Workbooks.Open Filename:=s1 + Range("D4") + pcode
+CStr(Range("Settings!G25")) + s3
Cells.Copy
Windows(MyWindow).Activate
Sheets("HistoryData").Cells.Select
Sheets("HistoryData").Paste
.....

Filename resolves to f.ex.
http://ichart.yahoo.com/table.csv?s=MSFT&d=5&e=12&f=2004&g=d&a=3&b=13&c=1986&ignore=.csv

This works fine for me, but on computers with Regional Settings that
specify "," as decimal symbol there is a problem. All data are put in
a single column.

Is there any way to temporarily change the decimal symbol setting?

Thx.
Lester
 
T

Tom Ogilvy

No, but if you rename the file to have a .txt extension and then use
opentext rather than open, you would have more control on how the file is
parsed. I assume the layout will be constant.

In excel 2002 and later, there are some settings associated with opening a
text file that might help. I don't have a copy handy, so I can't give you
specifics. I believe in the Workbooks.Open method, they have added a Locale
setting that might help. There were also some additions in the import text
area under the data =>Get External Data menu as I recall.

Dave Peterson had posted a comment relating to using the Text Import wizard:

xl2002 has an button (advanced...) on step 3 of the wizard that allows you
to
specify the decimal point character, the thousands character and how to
treat
trailing minuses.

so there is probably a setting for this as well in OpenText which is the vba
equivalent to the text import wizard - at least in terms of specifying the
settings.
 
L

Lester

Thank you, that could be a way forward, albeit somewhat slow. Trouble
is the data look as follows:

2-Jun-04,26.12,26.28,26.01,26.13,54020000,26.13
1-Jun-04,26.13,26.27,25.87,26.11,48369500,26.11
28-May-04,26.14,26.35,26.02,26.23,37393000,26.23
etc...

So the first dataset is a date in a format that will not translate
into other languages.

If the regional settings cannot be changed, can they be read so that I
can alert users of the problem?
 
Top