Comma-delimited files and Reional Settings
 

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=...86&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

Comma-delimited files and Reional Settings
 

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.

--
Regards,
Tom Ogilvy


<Lester wrote in message ...
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=...86&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

Comma-delimited files and Reional Settings
 

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?

On Sat, 12 Jun 2004 09:35:59 -0400, "Tom Ogilvy"
wrote:

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.

Comma-delimited files and Reional Settings
 

look in Excel VBA help at Application.International

--
Regards,
Tom Ogilvy

<Lester wrote in message ...
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?

On Sat, 12 Jun 2004 09:35:59 -0400, "Tom Ogilvy"
wrote:

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.

Comma-delimited files and Reional Settings
 

You also might want to look at Chip Pearson's site:

http://www.cpearson.com/excel/imptext.htm

--
Regards,
Tom Ogilvy

<Lester wrote in message ...
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?

On Sat, 12 Jun 2004 09:35:59 -0400, "Tom Ogilvy"
wrote:

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.