One more adjustment to the Excel sample after having become able to test the
sample against
different user interface languages of Excel with the conclusion, that in a
standard Excel
installation it is not possible to write a generic program that is able to have
Excel adjust a
formula according to the user interface language. As a result the Excel sample
program will now not
try to internationalize the sum formula (as it does not work), but rather give
the user a warning,
if Excel raises a "#NAME?" error with an advice on how to solve the problem,
additionally
demonstrating (which in case of an error makes all three values distinctive)
the sum cell's
properties "formula", "text" and "value".
The reason why there is so much attention placed on this facet is simple: in
Europe with many
non-English languages the Excel sample always caused the "#NAME?" error to be
displayed in the
produced Excel spreadsheet without any further information as to the cause and
to the remedy.
(Probably the original author was from an English speaking country and was not
aware of this
particular nasty problem with respect to the function names. It is strange that
Microsoft has never
resolved this in a straight-forward manner, though there exists a Microsoft
plugin for formula
translations to different languages.)
This is now the "samp09.rex" version as committed to trunk a few minutes ago,
such that one can test
it via copy and paste:
excelApplication = .OLEObject~new("Excel.Application")
excelApplication~visible = .true -- make Excel visible
Worksheet = excelApplication~Workbooks~Add~Worksheets[1]
colTitles = "ABCDEFGHI" -- define first nine column
letters
lastLine = 12 -- number of lines to process
/* HINT: if your local Excel user interface language is not English, you
may need to rename the
function name 'sum' to your user interface language, e.g. in
German to 'summe' */
sumFormula = "=sum(?2:?"lastLine-1")" -- English formula: question
marks will be changed to column letter
say "sumFormula: " sumFormula "(question marks will be changed to
column letter)"
do line = 1 to lastLine -- iterate over lines
do col = 1 to colTitles~length -- iterate over columns
colLetter = colTitles[col] -- get column letter
cell = Worksheet~Range(colLetter||line) -- e.g. ~Range("A1")
if line = 1 then do -- first row? yes, build title
cell~value = "Type" colLetter -- header in first row
cell~font~bold = .true -- make font bold
cell~Interior~ColorIndex = 36 -- light yellow
xlHAlignRight = excelApplication~getConstant("xlHAlignRight") -- get
right adjust constant
cell~style~horizontalAlignment = xlHAlignRight -- right adjust title
end
else if line = lastLine then do -- last row? yes, build sums
/* set formula, e.g. "=sum(B2:B9)" */
cell~formula = sumFormula~changeStr("?",colLetter) -- adjust formula
to column to sum up
cell~Interior~ColorIndex = 8 -- light blue
end
else do -- a row between 2 and 9: fill with random values
cell~value = random(999999) / 100 -- create a random decimal
value
cell~font~ColorIndex = 11 -- set from black to violet
end
end
end
-- check whether Excel's user interface language causes the "#NAME?"
error, if so advice
sumCell = WorkSheet~range("A"lastLine) -- get sum-cell of column A
if sumCell~text = "#NAME?" then
do
say
say "** Excel reports a '#NAME?' error for the 'sum' function! Probable
cause: **"
say "** your local Excel user interface language is not set to English,
therefore you need **"
say "** to adjust the function name 'sum' in the variable 'sumFormula'
to your user interface **"
say "** language and rerun this program (e.g. in German you need to
rename 'sum' to 'summe') **"
say "** sumCell~formula:" sumCell~formula
say "** sumCell~text: " sumCell~text
say "** sumCell~value: " sumCell~value
say
end
-- create a format string for our numbers, use thousands and decimal
separators
formatString =
"#"excelApplication~thousandsSeparator"##0"excelApplication~decimalSeparator"00"
say "formatString: " formatString -- show format string
excelApplication~useSystemSeparators = .false -- allow our format string
to be used everywhere
stringRange="A2:"colTitles~right(1)lastLine
say "formatting range:" stringRange
WorkSheet~range(stringRange)~numberFormat = formatString -- get range and
set its number format
-- make sure that file gets quietly overwritten in case it exists already
excelApplication~DisplayAlerts = .false -- no alerts from now on
/* save sheet in user's home directory */
homeDir = value("USERPROFILE",,"ENVIRONMENT")-- get value for environment
variable "USERPROFILE"
fileName = homeDir"\samp09_ooRexx.xlsx" -- build fully qualified
filename
say "fully qualified fileName:" fileName -- show fully qualifed filename
Worksheet~SaveAs(fileName) -- save file
-- let the user inspect the Excel file
say "Excel sheet got saved to file, press enter to continue ..."
parse pull . -- wait for user to press enter
excelApplication~Quit -- close Excel
---rony
_______________________________________________
Oorexx-devel mailing list
Oorexx-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/oorexx-devel
