Hi,

Background: I'm writing a C++ application which exports data to Excel
using Automation in Visual Studio C++ 6.0. I created a .dll which
includes all of the automation, which I just have to send an array of
data to and formatting instructions. I have one cell in each export
which is a formula. The formula (in Excel 2003 and Excel '97) turns
out to look like this in the exported spreadsheet:
"{=SUM(B1:B1999)}"

All of the numbers in column B are set as type "General". The sum
works in 2003, but not in '97. The fix for '97 is to do one of two
things:
1.) Retype all cells. "Return" and changing the type to "Number" do
not work...but retyping them all does.
2.) Change it to "{=SUM(B1:B1999+0)}" and then press "Ctrl + Shift +
Enter" while still in the cell.

What I need to know is, why does number 2 work? Is there anything else
I should try so this can be automated without human intervention?

Thanks guys (and gals)!

-Benry

Why do you need this as an array formula with the {} ?

Not sure if it will make a difference in XL 97, but just enter the formula
as "=SUM(B1:B1999)".

NickHK

"Benry" wrote in message
oups.com...
Hi,

Background: I'm writing a C++ application which exports data to Excel
using Automation in Visual Studio C++ 6.0. I created a .dll which
includes all of the automation, which I just have to send an array of
data to and formatting instructions. I have one cell in each export
which is a formula. The formula (in Excel 2003 and Excel '97) turns
out to look like this in the exported spreadsheet:
"{=SUM(B1:B1999)}"

All of the numbers in column B are set as type "General". The sum
works in 2003, but not in '97. The fix for '97 is to do one of two
things:
1.) Retype all cells. "Return" and changing the type to "Number" do
not work...but retyping them all does.
2.) Change it to "{=SUM(B1:B1999+0)}" and then press "Ctrl + Shift +
Enter" while still in the cell.

What I need to know is, why does number 2 work? Is there anything else
I should try so this can be automated without human intervention?

Thanks guys (and gals)!

-Benry

Hi,

Just know that when you import data in excel, sometimes the data is
considered as text (or not recognised yet)
Set a cell value to 1 and formated as general, copy it, select the range to
adjust then pastespecial/multiply.
This force the conversion to numbers/genral.
Regards
JY

"Benry" wrote in message
oups.com...
Hi,

Background: I'm writing a C++ application which exports data to Excel
using Automation in Visual Studio C++ 6.0. I created a .dll which
includes all of the automation, which I just have to send an array of
data to and formatting instructions. I have one cell in each export
which is a formula. The formula (in Excel 2003 and Excel '97) turns
out to look like this in the exported spreadsheet:
"{=SUM(B1:B1999)}"

All of the numbers in column B are set as type "General". The sum
works in 2003, but not in '97. The fix for '97 is to do one of two
things:
1.) Retype all cells. "Return" and changing the type to "Number" do
not work...but retyping them all does.
2.) Change it to "{=SUM(B1:B1999+0)}" and then press "Ctrl + Shift +
Enter" while still in the cell.

What I need to know is, why does number 2 work? Is there anything else
I should try so this can be automated without human intervention?

Thanks guys (and gals)!

-Benry

Nick,

It doesn't make a difference. The exported function is formatted that
way regardless of what automation function I use. The curly brackets
can't be removed except manually in '97.


NickHK wrote:
Why do you need this as an array formula with the {} ?

Not sure if it will make a difference in XL 97, but just enter the formula
as "=SUM(B1:B1999)".

NickHK

"Benry" wrote in message
oups.com...
Hi,

Background: I'm writing a C++ application which exports data to Excel
using Automation in Visual Studio C++ 6.0. I created a .dll which
includes all of the automation, which I just have to send an array of
data to and formatting instructions. I have one cell in each export
which is a formula. The formula (in Excel 2003 and Excel '97) turns
out to look like this in the exported spreadsheet:
"{=SUM(B1:B1999)}"

All of the numbers in column B are set as type "General". The sum
works in 2003, but not in '97. The fix for '97 is to do one of two
things:
1.) Retype all cells. "Return" and changing the type to "Number" do
not work...but retyping them all does.
2.) Change it to "{=SUM(B1:B1999+0)}" and then press "Ctrl + Shift +
Enter" while still in the cell.

What I need to know is, why does number 2 work? Is there anything else
I should try so this can be automated without human intervention?

Thanks guys (and gals)!

-Benry

Jean-Yves wrote:
Hi,

Just know that when you import data in excel, sometimes the data is
considered as text (or not recognised yet)
Set a cell value to 1 and formated as general, copy it, select the range to
adjust then pastespecial/multiply.
This force the conversion to numbers/genral.

Yes, but the "Ctrl + Shift + Enter" is a force calculate. I need an
automated way to do this, I can't expect the user to do all of the
things listed above, and to automate that in my .dll would be very
hacked.

Thanks though,
Benry

No, "Ctrl + Shift + Enter" means you are entering your formula as a formula
array.
That why it seemed strange for a simple SUM function.
Application/Worksheet/Range.Calculate will force a recalculation.
Application.CalculateFull for everything.

NickHK

"Benry" wrote in message
ups.com...

Jean-Yves wrote:
Hi,

Just know that when you import data in excel, sometimes the data is
considered as text (or not recognised yet)
Set a cell value to 1 and formated as general, copy it, select the
range to
adjust then pastespecial/multiply.
This force the conversion to numbers/genral.

Yes, but the "Ctrl + Shift + Enter" is a force calculate. I need an
automated way to do this, I can't expect the user to do all of the
things listed above, and to automate that in my .dll would be very
hacked.

Thanks though,
Benry