Renaming Excel spreadsheets via DDE in national versions of Excel(%sastoxl macro)

Renaming Excel spreadsheets via DDE in national versions of Excel(%sastoxl macro)

Post by Arjen Raatelan » Sat, 22 Feb 2003 18:11:06

Quote:> I am trying to use the %sastoxl macro by Koen Vyverman to export sas datasets to (my german version of) excel. Unfortunately the use of excel macros to rename sheets and so on cause problems in national versions.

I've done some experimenting with the newest version of the SASTOXL
macro on our Finnish Excel 97 version.

1. DDE commands sent directly to Excel can be as for the
international/english version. No translating is necessary (e.g. the old
SASTOXL version, which doesn't use a macro sheet, works OK). Only the
names of sheets need to be adapted, like 'Sheet1' will have to be
'Taul1' in the local Finnish version (taulukko means table).

2. However in a default macro sheet (type 3) one needs to translate the
DDE functions, literals, delimiter used in argument lists, and perhaps
more to suit the local language version. I haven't found any documents
with equivalents.

3. It is actually possible to open an international macro sheet in the
Finnish language version (type 4). One would expect that such a sheet
would work as in the international language version, but I found this is
not so.

Firstly in the Finnish version the name of this table is Makro1 with a k
instead of a c.

Secondly, although the names of functions should be in 'english',
logical values true and false still have to be written in Finnish. If
you do write =halt(true) to the macro sheet through DDE, the word 'true'
will be quoted by Excel and that way it doesn't work at all. Only a
mixed language '=halt(tosi)' will eventually be recognised. (Tosi is
Finnish for true.)

Thirdly the macros you do manage to write to the international
Makro1-sheet work only after you (manually) confirm every line of them
clicking the green V on the entry line. Only then Excel recognizes the
functions and changes the keywords to uppercase. AFAIK this is different
from the behaviour of a default macro sheet.

I was not able to guess translations of all functions used, so I looked
for another way.
I found that if you do want to find out what the local language version
of a macro command is, you can proceed as follows:
Open an international macro sheet. Enter your macro text (with the
proviso as in the above about logical values). Copy the contents of the
cells and paste them to a local language macro sheet. Excel will do the
translating for you.

I'm not very familiar with Excel so perhaps that's why I had all kind of
awkward problems while doing this translating trick, but in principle it
seems to work somehow like I described.
There is also help available for the local language version old-style
macro functions (not in the general help but starting from the macro
sheet itself). However there was no list of function name equivalents
for the local and the international language versions.

All in all it started to look like a mine field and I dropped the idea
of adopting/adapting the newer version of SASTOXL for the time being.

Perhaps Koen Vyverman will chip in on this, too, because I know he has
run into the same problems as well. For somebody working for an
international market with many different language versions this seems
like a real nightmare.

I hope this helps, but I'm pretty sceptical about what Excel has to
offer here.
Arjen Raateland
Finnish Environment Institute SYKE
Research Dept.
SAS Support
phone +358 9 4030 0350