Solved

can not edit an excel worksheet using excel automation

Posted on 2003-12-01
7
896 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Mafalda
[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
7 Comments
 
LVL 8

Expert Comment

by:mnashadka
ID: 9850187
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
 
LVL 6

Author Comment

by:Mafalda
ID: 9851839
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
 
LVL 8

Expert Comment

by:mnashadka
ID: 9851900
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:DanRollins
ID: 9856862
>> 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
 
LVL 6

Author Comment

by:Mafalda
ID: 9857647
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
 
LVL 5

Expert Comment

by:migoEX
ID: 10311486
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
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 10364453
PAQed, with points refunded (250)

PashaMod
Community Support Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C++ assignment question 7 268
boost::uuid crashes 17 41
Can Live bindings change TGrid Cell Colour ? 1 34
Need some help with design 17 22
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
Many modern programming languages support the concept of a property -- a class member that combines characteristics of both a data member and a method.  These are sometimes called "smart fields" because you can add logic that is applied automaticall…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

726 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