Excel 2002 VC++ COM Automation - Read/Write Help

Hi Experts!

I am EXTREMELY new to this whole automation stuff so I wll try to be as descriptive as possible with my problem.

First - My Objective - I have an excel file which contains a bunch of data in a certain format (this is my input). I want to extract this data into my app/C++ code and do some conditional statements, manipulate some of the data I've collected and then write it to another excel sheet (my output file).

Secondly - What I have accomplished thus far - I have been able to successfully open both excel files for reading/writing. What I've gotten stuck on is all of the specifics as far as methods/functions for properly reading/writing data. In other words, I don't know how to say - go to cell A10 and grab the interger/string value.

Thirdly - I have already read through most of the MSDN documentation and a lot of it has gone over my head so I would really really appreciate specific examples from any of you if possible.

Lastly - Here is the code I have thus far. I would appreciate input regarding wether or not I'm going in the right direction, and if so, where am I going wrong or where can I go from here. I can successfully write the header portion of my output file but after that it kinda goes downhill. Essentials I would like to know how to do is read the data in different types i.e. strings/ints/doubles etc. Help is GREATLY appreciated! Thanks!

#include <iostream>

// In the #include area,
// import the type library information
#import "C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL"

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

#import "C:\Program Files\Microsoft Office\Office10\Excel.exe" \
  rename("DialogBox", "DialogBoxXL") \
  rename("RGB", "RGB_XL") \
  named_guids \

// In your function
int main()
  CoInitialize(NULL); // Initialize COM
        Excel::_ApplicationPtr apps(__uuidof(Excel::Application)) ;
        Excel::_WorkbookPtr workbook_in;
      Excel::_WorksheetPtr worksheet_in;
      Excel::_WorkbookPtr workbook_out ;
      Excel::_WorksheetPtr worksheet_out ;

        workbook_in = apps->Workbooks->Open("c:\\Test_In.xls"); // Open the workbook
        worksheet_in = workbook_in->Sheets->Item[1l];
      workbook_out = apps->Workbooks->Add() ;
      worksheet_out = workbook_out->ActiveSheet ;
        for (long i=1; i <= 65536; i++) {
             if(i == 1) //write the header portion of the file
                   worksheet_out->Cells->Item[i][1L] = "Person" ;
                   for(long j=1; j <= 128; j++){
                        TCHAR szBuffer[100];
                        wsprintf (szBuffer, "Attribute%d", j);
                        worksheet_out->Cells->Item[i][j] = szBuffer;

             { if( i == 2 || i == 3 || i == 4)
                        TCHAR szBuffer[100];
                        //is this how I get the value from the sheet?
                        wsprintf (szBuffer, "%d", worksheet_in->Cells->Item[i][4L].intVal);
                        worksheet_out->Cells->Item[i][4L]  = szBuffer;      

      _variant_t vnull, vformat, vconflict ;

      vformat = (long)Excel::xlWorkbookNormal ;
      vconflict = (long)Excel::xlLocalSessionChanges ;
// format
// password
// write-res password
// read-only recommended
// create backup ?
// access mode
// conflict resolution
// add to MRU
                   vnull,vnull) ;                  
// not used

      workbook_in->Close() ;
        workbook_out->Close() ;
      apps->Quit () ;
    //Excel::_ApplicationPtr excel;
    //HRESULT hr = excel.CreateInstance(L"Excel.Application"); // Start excel
    // // Get the first worksheet
      //std::string worksheet_name = static_cast<char *>(worksheet->Name); // Get the worksheet name
    //workbook->Close(); // Close the workbook
    //excel->Quit(); // Quit Excel
  catch(_com_error &ce)
    // Error occurred
  return 0;
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.

Have you considered using the MFC Automation classes?
DizZzMAuthor Commented:
I tried it briefly yesturday and this COM method was what actually got me to the point I'm at now so i figured I'd just stick with it.

I've actually made some progress except now I'm stuck with the writing part. I have strings of characters that represent words... i.e "00AB" and when I go to assign it to worksheet_out->Cells->Item[i][1L], it gets converted to like an integer or something. Is there a way to keep it as a hex word?
To read in a value, you would use something in the Range member:
_variant_t data_in = worksheet_in->Range["A1"]->Value;
Then whatever was in there will be stored in data_in

To write, you would also use the Range member of Worksheet:
worksheet_out->Range["B1"] = 4321;

I hope this helps.  Good luck!
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Sorry, I just thought about it, and you can't assign an int directly; it would have to be a long, double, etc:
worksheet_out->Range["B1"] = 4321l;
worksheet_out->Range["B1"] = 4321.778;
worksheet_out->Range["B1"] = "4321";
DizZzMAuthor Commented:
Hi mnashadka! I tried what you've said and it didn't quite work for me. I'm almost at the point where I have everything working though. I'm having a problem though when I do an assignment...

worksheet_out->Cells->Item[i][j] = "0E51" for example gives me a scientific notation 0.0 in the excel cell instead of the "0E51" string I want to write. Any ideas why this is? And if so, how can i correct the problem?

Ok, since 0E51 is scientific notation for 1x10^51, you would actually have to change the number format to text (just like right-clicking on the worksheet, clicking Format, and then choosing Text.  To do this, call PutNumberFormat with the @ to tell Excel that it's text.  Here's an example of how that might work:

    Excel::RangePtr range = worksheet_out->Cells->Item[5][5];
    range->Value = "0E51";

I tried it with your code, and it does work on my system.  Good luck.

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
By the way, if I ever have trouble figuring out how to do something in Excel, I record a macro in Excel, do what I'm trying to do in code manually, and then look at the generated VB code.  This usually translates really closely to what I need to do in C++.
DizZzMAuthor Commented:
Awsome. Thanks so much for your help!
Hi DizZzM,

I wonder if you can help me with opening excel files for reading/writing?
I would appreciate me very much if you can help. thanks!
Hi Dizzzm,
i tried your code to open an xls file and close it
but the compiler says i have problems in the writen existing files
COMIP.H spesific.
do u have any idea what could be the prob andhow to solve it?
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

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.