How to write a language independent formula
Hi all...
I am using Excel COM component to transform a CSV file into an XLS file. After converting it, I need to summarize a column that contains numbers. I am using FormulaR1C1 property of a Cell object. In order to know what value I need to place in that property, I used Excel by recording a new macro and make the sum. When I saw the generated code, FormulaR1C1 property was assigned this value : "=SUM(R[-488]C:R[-1]C)". I have Microsoft Excel in Spanish, so I thought that formula was writen in a standard way. Well.. when I used the same code from my component I got an exception. After some tests, I discovered that I should assign "=SUMA(F(-488)C:F(-1)C)" to FormulaR1C1 property (that is, the formula written in spanish). By using this formula would work for users that have Excel in Spanish, but not for users that have Excel in English. Is there any way to solve this problem? Thanks a lot in advance Jaime |
How to write a language independent formula
I don't know how you write the formula to a cell, but from VBA you would
indeed use the English version. Otherwise you would use the FormulaR1C1Local property. Are you sure this is how it happened? -- Kind regards, Niek Otten "Jaime Stuardo" wrote in message ... Hi all... I am using Excel COM component to transform a CSV file into an XLS file. After converting it, I need to summarize a column that contains numbers. I am using FormulaR1C1 property of a Cell object. In order to know what value I need to place in that property, I used Excel by recording a new macro and make the sum. When I saw the generated code, FormulaR1C1 property was assigned this value : "=SUM(R[-488]C:R[-1]C)". I have Microsoft Excel in Spanish, so I thought that formula was writen in a standard way. Well.. when I used the same code from my component I got an exception. After some tests, I discovered that I should assign "=SUMA(F(-488)C:F(-1)C)" to FormulaR1C1 property (that is, the formula written in spanish). By using this formula would work for users that have Excel in Spanish, but not for users that have Excel in English. Is there any way to solve this problem? Thanks a lot in advance Jaime |
How to write a language independent formula
Yes... by using VBA I have no problems (when it is used from VB editor in
Excel). As I said, I am using Microsoft Excel Object Library, instantiating Excel.Application object from a C++ program (using IDispatch interface). When I assigned "=SUM(R[-488]C:R[-1]C)" to FormulaR1C1 property, an exception occurs, but when I used "=SUMA(F(-488)C:F(-1)C)" it worked. As you know, if I would use "=SUM(R[-488]C:R[-1]C)" in VBA using Excel editor, I would have no problems. I realized of that by using the following test. I have created an XLS file with a SUM formula at cell (1, 1). Then I opened that file from my C++ application and read the Cell (1, 1) content. I see that the string read from FormulaR1C1 property is the Spanish version of the formula, contrary to the version that is shown in the VBA editor. Jaime "Niek Otten" wrote: I don't know how you write the formula to a cell, but from VBA you would indeed use the English version. Otherwise you would use the FormulaR1C1Local property. Are you sure this is how it happened? -- Kind regards, Niek Otten "Jaime Stuardo" wrote in message ... Hi all... I am using Excel COM component to transform a CSV file into an XLS file. After converting it, I need to summarize a column that contains numbers. I am using FormulaR1C1 property of a Cell object. In order to know what value I need to place in that property, I used Excel by recording a new macro and make the sum. When I saw the generated code, FormulaR1C1 property was assigned this value : "=SUM(R[-488]C:R[-1]C)". I have Microsoft Excel in Spanish, so I thought that formula was writen in a standard way. Well.. when I used the same code from my component I got an exception. After some tests, I discovered that I should assign "=SUMA(F(-488)C:F(-1)C)" to FormulaR1C1 property (that is, the formula written in spanish). By using this formula would work for users that have Excel in Spanish, but not for users that have Excel in English. Is there any way to solve this problem? Thanks a lot in advance Jaime |
All times are GMT +1. The time now is 04:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com