Excel Automation:Manual Calculation problem

Hi Experts,

I want to do Manual Calculation in Excel Automation with VC++/MFC. To do the equivalence of Excel/VBA

Application.Calculation = xlCalculationManual

I thought I could use

excel::_Application oExcel;
oExcel.SetCalculation((long)-4135);

Compilation goes fine, but error in run time. What's wrong here?

Thanks.
jper_hmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mahesh1402IT ProfessionalCommented:
try this

vInt(calcn, -4135);
oExcel.SetCalculation(calcn);

SetCalculation() sets "calculation mode" of excel's active workbook.ie automatic or manual

check return value of function....
values are as follows
1 is for success
-1 / -2 excel/workbook not active
-3 unknown error

what value you r geting ? also what runtime error message you are getting ?

MAHESH
jper_hmAuthor Commented:
Thanks. What is vInt()? Which header file should I include?

JPER
mahesh1402IT ProfessionalCommented:
what error u r getting .. pass it as a int

MAHESH
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

mahesh1402IT ProfessionalCommented:
try

oExcel.SetCalculation(COleVariant((long)-4135));

MAHESH
jper_hmAuthor Commented:
I cannot compile with vInt() nor oExcel.SetCalculation(COleVariant((long)-4135));

vInt is not declared. I thought I should include some header file.

oExcel.SetCalculation(COleVariant((long)-4135)); is just not right. SetCalculation parameter is long and not VARIANT.
mahesh1402IT ProfessionalCommented:
vInt was by mistake.....

but what error code you are getting ? -1 / -2 / -3 ?? an also whats ur runtime error ?

MAHESH
mahesh1402IT ProfessionalCommented:
also post some more code
jper_hmAuthor Commented:
try
oExcel.SetCalculation(COleVariant((long)-4135));
get this compilation error:
'SetCalculation' : cannot convert parameter 1 from 'class COleVariant' to 'long'

Since I cannot run it, I do have the return value of the function.

There is nothing unique about my original code:
excel::_Application oExcel;
excel::Workbooks books;
excel::_Workbook book;
excel::Worksheets sheets;
excel::_Worksheet sheet;
excel::Range range, range1, cols;
oExcel.SetDisplayAlerts(0);
oExcel.SetVisible(TRUE);      
oExcel.SetUserControl(TRUE);
oExcel.SetScreenUpdating(FALSE); //to speed up update macro code
oExcel.SetCalculation((long)-4135); //xlCalculationManual=-4135

When it comes to the last line, an error ocurrs, saying "Runtime error! ...abnormal program termination"
mahesh1402IT ProfessionalCommented:
I am asking about your original error....  when u compile your code with just

excel::_Application oExcel;
oExcel.SetCalculation((long)-4135);


what is the code returned ? and also whats the run time error ?

MAHESH
jper_hmAuthor Commented:
The error is "Runtime error! ...abnormal program termination".

If I step through the code, when it gets to oExcel.SetCalculation((long)-4135); I get "unhandled exception".

Since I get an error, I cannot get any returned code!
mahesh1402IT ProfessionalCommented:
Do not set mode at start.....you need to have a visible workbook to change this setting.

MAHESH

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jper_hmAuthor Commented:
I have already made the workbook visible and controllable.

oExcel.SetVisible(TRUE);    
oExcel.SetUserControl(TRUE);
mahesh1402IT ProfessionalCommented:
i.e. 1st Activate Workbook and Worksheet and then Set calculation mode..

MAHESH
mahesh1402IT ProfessionalCommented:
you need to open workbook and worksheet.. from your above code it seems you do not have any worksheet open in excel.. activate worksheet 1st and then set calculation mode

MAHESH
jper_hmAuthor Commented:
Mahesh, you are right. If SetCalculation() is done after opening a worksheet, it works fine.

Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.