Solved

CWebCalc, CWorksheet, CRange ... (spreadsheet in MFC)

Posted on 2002-05-23
6
1,420 Views
Last Modified: 2013-11-25
Hi all, I've added into my project the Microsoft Office Shpreadsheet object. I've found some example in Visual Basic but I can't find any piece of code for using it with MFC. Can you suggest me where to find documentation and complete examples?
Thanx, Michele
0
Comment
Question by:css
  • 3
  • 3
6 Comments
 
LVL 2

Expert Comment

by:christophm
ID: 7030635
Hi css/Michele,

I have a comprehensive explanation on Internet at
   www.teleport.com/~millercg

click the link on my page
   Run/Manipulate XL from C++ w/MFC

Also be sure to read MSDN Technical Article "Automating
Microsoft Office 97 and Microsoft Office 2000" by Lori Turner of Microsoft.

good luck - christophm
0
 

Author Comment

by:css
ID: 7031426
Hi christophm, what I need now is only the solution to this: I want to add a formula to some cells, like:
=FORMULA(x,y);
This formula doesn't exist in Excel so in the cell I see the value "=NAME?". I want to mantain the cell formula but to change the returned value.
How can I do that?
0
 
LVL 2

Expert Comment

by:christophm
ID: 7033059
Hi css,
1. If you add a formula that is known to XL then everything works well.

2. If you add a formula that is not known to XL then:
   - the formula is put in the target cell(s)
   - the display in XL will indeed show "=NAME?"

3. You say, "I want to maintain the cell formula but to change the returned value."     I don't know what you mean by that.

If what you want to do is have a function (written in C++/MFC) that can be initiated from XL to compute a value that can be subsequently pasted into an XL cell what I would do is:

Write an automation component, put the formula in that component as an exposed method, put Get/Set properties in the component to manage the values used by the formula, register the component, add a control (button?) to the spreadsheet and in the VBA code behind the button create an instance of the component, call the Set(s) to set the variables and call the 'formula' method to do the computation.  In the VBA code - place the return value of the function (method) in the target XL cell.

This sounds complicated but it's really very easy - if you are at all familiar with VB/VBA you will find that the syntax of calling the automation components exposed methods and properties from XL VBA to be standard VB syntax.

Let me know if this is what you want or if your are trying to do something else.

Good Luck ! - christophm


0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:css
ID: 7036588
This is a bit different from what I would like to do. In my project I don't handle excel but the OWC spreadsheet component.
I've added it in my project (in a dialog) as ActiveX control. I can handle some event like "OnCalculate, etc.".
What I need to do is:
- I insert the "=Tick(a,b)" in the spreadsheet
- Now I see "=NAME?" as result
- I want to catch it in the OnCalculate event
- return a custom value instead of the "=NAME?"
- If I set the cell value I write over the formula but I have to mantain it because when I "tick" comes I need to know where the "=Tick(a,b)" formula is.

Thanx, Michele
0
 
LVL 2

Accepted Solution

by:
christophm earned 200 total points
ID: 7043667
Hi css/Michele,  

In summary; Microsoft Office Spreadsheet (OWC) added to dialog as an Ax control.  Invalid formula in the spreadsheet and then in the 'OnCalculate()' function you wish to find the cell with the 'bad' formula.

I poked around with this a bit and the best I can do is:
('m_MySpreadsheet' is the spreadsheet data member in your dialog class, this code from the 'OnCalculate()' function member of the spreadsheet object/class.)

void CTestDlg::OnCalculateSpreadsheet1(LPDISPATCH EventInfo)
{
  CRange  crA_Cell = m_MySpreadSheet.GetRange(COleVariant("A1"), COleVariant("A1"));
VARIANT vrA1 = crA_Cell.GetValue();
}

if     vrA1.vt = VT_BSTR, and vrA1.bstrVal contains "#NAME?"  then cell A1 has an invalid formula.  I could not make the VARIANT contain the real underlying value   "=Tick(a,b)"    but there's a whole lot of functions (in the CRange class) and perhaps one of them will do it for you.  You can put the 'GetRange()' in a loop and look through cells until you find the 'bad' formula containing cell, but I agree that's not very elegant.

You know that if you go back to Project, Insert Into Project, Components and Controls, and highlight the Microsoft Office Spreadsheet Ax control then click "More Info" you will get the help file.  That's the good news, the bad news is that the code samples are  in VB.

If this doesn't solve your problem I suggest you delete your question in EE and ask again.  The length of our back and forth discussion may discourage others who could help you from participating.

Wish you success - chris-m /   millercg@teleport.com
0
 

Author Comment

by:css
ID: 7043893
Hi Christoph, I've made a long research and the result is that you can't do what I want. The solution was a trick that I explain below but I appriceated your help so I give you points...

void CExcelDialog::OnChangeSpreadsheet(LPDISPATCH EventInfo)
{
     // TODO: Add your control notification handler code here

     // Disable the events so can change values
     WaitForSingleObject(HANDLE(m_lock),INFINITE);
     m_lock.Lock();

     m_spreadsheet.SetEnableEvents(false);

     CRange cell=m_spreadsheet.GetActiveCell();
     CString formula=cell.GetFormula();

     if (IsMosaicFormula(formula)) {
          // It's in form "=Tick(symbol,market)"
          CRange new_cell;
          CString new_formula=ConvertFormula(formula);

          VARIANT v1;
          VariantInit(&v1);
          v1.vt = VT_BSTR;
          v1.bstrVal = new_formula.AllocSysString();
          cell.SetValue(v1);
          SysFreeString(v1.bstrVal);
          //VariantClear(&v1);

          CheckWhatToSubscribe();
     }
     m_spreadsheet.SetEnableEvents(true);

     m_lock.Unlock();
}

void CExcelDialog::OnStartEditSpreadsheet(LPDISPATCH EventInfo)
{
     // TODO: Add your control notification handler code here
     WaitForSingleObject(HANDLE(m_lock),INFINITE);
     m_lock.Lock();

     m_spreadsheet.SetEnableEvents(false);

     CRange cell=m_spreadsheet.GetActiveCell();
     CString formula=cell.GetFormula();

     if (IsMosaicFormulaBack(formula)) {
          // It's in form "=IF(0,"symbol|market",something)"
          CRange new_cell;
          CString new_formula=ConvertFormulaBack(formula);

          VARIANT v1;
          VariantInit(&v1);
          v1.vt = VT_BSTR;
          v1.bstrVal = new_formula.AllocSysString();
          cell.SetValue(v1);
          SysFreeString(v1.bstrVal);
          //VariantClear(&v1);
     }
     m_spreadsheet.SetEnableEvents(true);

     m_lock.Unlock();
}


CString CExcelDialog::ConvertFormulaBack(CString formula) {
     CString symbol;
     CString field;

     symbol=ExtractSymbolFromFormulaBack(formula);
     field=ExtractFieldFromFormulaBack(formula);

     CString new_formula=CreateFormula(symbol,field);
     return new_formula;
}

CString CExcelDialog::ConvertFormula(CString formula) {
     CString symbol;
     CString field;

     symbol=ExtractSymbolFromFormula(formula);
     field=ExtractFieldFromFormula(formula);

     CString new_formula=CreateDummyFormula(symbol,field,"");
     return new_formula;
}
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now