Solved

can not edit an excel worksheet using excel automation

Posted on 2003-12-01
7
888 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
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
What Security Threats Are You Missing?

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.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
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…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

758 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

22 Experts available now in Live!

Get 1:1 Help Now