Solved

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

Posted on 2002-05-23
6
1,426 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VS2015 compilation and missing DLLs 9 114
wait notify demo infinite loop 3 96
sumHeights  challenge 17 66
What are the big features of MVC5? 4 72
Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
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…

911 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

18 Experts available now in Live!

Get 1:1 Help Now