Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2002-05-23
6
Medium Priority
?
1,465 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 600 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
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…

670 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