can not edit an excel worksheet using excel automation

In the following program I am trying to modify the contents of an Excel worksheet.
The workbook or worksheets are not protected and it is possible to edit the file manually.
I work with Office XP on an Win XP Pro operating system.
I use VC++ .NET
I can exctract the values of the fields but I can not modify/insert them.
I get the error message "Type Missmach" nomatter what the type of the cell is or the type of the value I try to assing is !
I tried integers, floats, doubles char * and _bstr_t to no avail ...

This is the code I am using:

#include <iostream>
#include <string>
#include <sstream>

// In the #include area,
// import the type library information

#import "C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL" \
 rename("RGB", "OfficeRGB") \
 rename("CopyFile", "OfficeCopyFile") \
 exclude("DocumentProperties") \
 no_namespace

#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"

#import "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" \
 rename("DialogBox", "ExcelDialogBox") \
 rename("RGB", "ExcelRGB") \
 rename("CopyFile", "ExcelCopyFile") \
 no_dual_interfaces

using std::string;
using std::cout;
using std::endl;
using std::stringstream;

namespace
{
  string to_string(_variant_t v)
  {
    stringstream ss;
    ss << _bstr_t(v);
    return ss.str();
  }
  float to_float(_variant_t v)
  {
    float f;
    stringstream ss;
    ss << _bstr_t(v);
    ss >> f;
    return f;
  }
}
// In your function
int main()
{
 CoInitialize(NULL); // Initialize COM
 Excel::_ApplicationPtr excel;
 try
 {
   // Start excel
   HRESULT hr = excel.CreateInstance(L"Excel.Application");
   // Open the workbook
   Excel::_WorkbookPtr workbook = excel->Workbooks->Open("C:\\Documents and Settings\\Mafalda\\My Documents\\Ptop\\T-019.xls");
   // Get the first worksheet
   Excel::_WorksheetPtr worksheet = workbook->Sheets->Item[1l];
   // Get the worksheet name
   string worksheet_name = static_cast<char *>(worksheet->Name);
   string customer_id = to_string(worksheet->Range["A24"]->Value);
   cout.precision(3);
   cout << "Customer ID: " << customer_id << endl;
   cout << "Customer ID: " << to_float(worksheet->Range["E24"]->Value) << endl;
   cout << "Customer ID: " << to_float(worksheet->Range["F24"]->Value) << endl;
   cout << "Customer ID: " << to_float(worksheet->Range["G24"]->Value) << endl;
   string t = "my text";
   worksheet->Range["E57"]->Value = t.c_str();
   // Close the workbook
   workbook->Close();
   // Quit Excel
   excel->Quit();
 }
 catch(_com_error &ce)
 {
   // Error occurred
   cout << ce.ErrorMessage() << endl;
   // Quit Excel
   excel->Quit();
 }
 CoUninitialize();
 return 0;
}

Output example:

Customer ID: D639
Customer ID: 0.281
Customer ID: 3.81
Customer ID: 110
Type mismatch.
Press any key to continue
LVL 6
MafaldaAsked:
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.

mnashadkaCommented:
I tried your code and didn't have any problems with the insert.  I do have a problem with the _ApplicationPtr object being declared outside of the try block.  It won't call Release until it goes out of scope, which is after the CoUninitialize(), and this causes a GPF.  Either put that inside another block (set of {}), or declare it inside the try.  With what you're doing inside the catch, you probably want to put inside another block.  Also, you're not saving the workbook, so you might want to call Save.  If that doesn't fix your issue, you might want to post your excel spreadsheet somewhere that we can see it.  Good luck.

// In your function
int main()
{
  CoInitialize(NULL); // Initialize COM
  {
    Excel::_ApplicationPtr excel;
    try
    {
      // Start excel
      HRESULT hr = excel.CreateInstance(L"Excel.Application");
      // Open the workbook
      Excel::_WorkbookPtr workbook = excel->Workbooks->Open("C:\\Documents and Settings\\Mafalda\\My Documents\\Ptop\\T-019.xls");
      // Get the first worksheet
      Excel::_WorksheetPtr worksheet = workbook->Sheets->Item[1l];
      // Get the worksheet name
      string worksheet_name = static_cast<char *>(worksheet->Name);
      string customer_id = to_string(worksheet->Range["A24"]->Value);
      cout.precision(3);
      cout << "Customer ID: " << customer_id << endl;
      cout << "Customer ID: " << to_float(worksheet->Range["E24"]->Value) << endl;
      cout << "Customer ID: " << to_float(worksheet->Range["F24"]->Value) << endl;
      cout << "Customer ID: " << to_float(worksheet->Range["G24"]->Value) << endl;
      string t = "my text now";
      worksheet->Range["E57"]->Value = t.c_str();
      workbook->Save();
      // Close the workbook
      workbook->Close();
      // Quit Excel
      excel->Quit();
      excel->Release();
      excel = NULL;
    }
    catch(_com_error &ce)
    {
      // Error occurred
      cout << ce.ErrorMessage() << endl;
      // Quit Excel
      excel->Quit();
      excel->Release();
      excel = NULL;
    }
  }
  CoUninitialize();
  return 0;
}
0
MafaldaAuthor Commented:
mnashadka,
The GPF was caused because I posted an intermediate version where I pulled out of scope the Excel::_ApplicationPtr in order to be able to close the application when an exception occurs.

So it was a problem but not the problem I am trying to solve.

I created a new XLS file and only tried to change one cell's value but got the same error:
Type mismatch.

I am really frustrated ... what could be wrong ?

I have Excel 2002 with SP-2
0
mnashadkaCommented:
I can only test it with Excel 2000 since that's what I have here, but could you post the Excel spreadsheet somewhere that we could all see it?  I doubt that it's the problem, but please post that as well as the final version of the code.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

DanRollinsCommented:
>> I tried integers, floats, doubles char * and _bstr_t to no avail ...

What does the header file say that you need to pass?  
I'll guess that it wants to see a VARIANT... so try constructing a _variant_t and using that in your assignment statement.

-- Dan
0
MafaldaAuthor Commented:
OK, I solved the mistery !!!

First I read some articles about localization issues and added support for local-independent meening realy swiching to en-US (or any other) before accessing EXCEL and then switching back.

It helped to solve some GPFs I had

Then I read some articles and in there I found that in C# you must access Value2 and not Value ...

Well I was really frustrated ... tried it in my C++ program and VOILA IT WORKS like a charm
 
I will continue investigating this issue until I reach it's roots ;o)

All articles discussing this issue addressed VC++ .NET so this new version must be the main problem ...


Mafalda
0
migoEXCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ - Refund

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

migoEX
EE Cleanup Volunteer
0
PashaModCommented:
PAQed, with points refunded (250)

PashaMod
Community Support Moderator
0

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
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
C++

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.