Thread: c++ for excel worksheets

  1. #1
    Registered User
    Join Date
    Feb 2003
    Posts
    28

    c++ for excel worksheets

    Is it possible to write macros for excel worksheets using Visual C++ ?? For example I would like to search for text in multiple columns and count occurrences and things like that.
    Thanks
    Michael

  2. #2
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,793
    Yes...its pretty easy for VC++ due to the magical "#import" directive which can take type libs and produce some prtty good wrapper classes for you...

    You need to import a few typelibs for automation and office itself - VBE6EXT.olb and mso.dll. then import the executable for excel itself (the newer version of excel have the type lib info as part of the exe....)

    Then, initialise com and use the wrapper classes inside a try/catch blcok and watchout for _com_error exceptions

    This works for me with ExcelXP (Excel10)

    Code:
    #pragma warning (disable:4146)
    #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", "ExDialogBox") rename("RGB", "ExRGB")
    
    #include <windows.h>
    #include <iostream>
    
    
    
    int main(){
    
    	CoInitialize(0);
    
    	try{
    		Excel::_ApplicationPtr lpApp("Excel.Application");
    		Excel::_WorksheetPtr lpWkst = 0;
    		
    		lpApp->PutVisible(0,TRUE);
    
    		lpApp->Workbooks->Add();
    		lpWkst = lpApp->ActiveSheet;
    
    		lpWkst->Range["A1"]->Value2 = "Forename";
    		lpWkst->Range["B1"]->Value2 = "Score";
    		lpWkst->Range["A2"]->Value2 = "Dave"; 
    		lpWkst->Range["B2"]->Value2 = 40L;
    		lpWkst->Range["A3"]->Value2 = "Andrew";
    		lpWkst->Range["B3"]->Value2 = 35L;
    		lpWkst->Range["A4"]->Value2 = "Simon";
    		lpWkst->Range["B4"]->Value2 = 29L;
    
    		lpWkst = 0;
    		lpApp = 0;
    
    	}
    	catch(_com_error& e){
    
    		std::cout << "Error ";
    		std::cout << static_cast<char*>
    			((e.Description().length() ? e.Description() : ""));
    		return 1;
    	}
    
    	CoUninitialize();
    
    	return 0;
    }

  3. #3
    Registered User
    Join Date
    Feb 2003
    Posts
    28

    excel c++

    Thanks a lot. Not everything is clear to me. Is there a written document which explains all this in more detail?
    I have excel 2000.
    Can this work as a console c++ separate program ?
    Does the wildcard* exist in c++ and can it be used here?
    Thanks
    Michael

  4. #4
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,793

    Re: excel c++

    Originally posted by m.albert
    Thanks a lot. Not everything is clear to me. Is there a written document which explains all this in more detail?
    I have excel 2000.
    For automation in VC++ there isnt much....best bet is sites like MSDN and Codeguru.com

    Originally posted by m.albert
    Can this work as a console c++ separate program ?
    Yeah, sure....my example runs with a console

    Originally posted by m.albert
    Does the wildcard* exist in c++ and can it be used here?
    Thanks
    Michael
    Er....in what context?.....I dont understand

  5. #5
    Registered User
    Join Date
    Feb 2003
    Posts
    28

    excel

    A wildcard to look for parts of a word in a cell: for example if I have a worksheet with column 1 (name) 2 (year of birth) and I want to know hw many lines there are where there is Joan or Joanne (so using a wild card Joan*) in comumn 1 and who are born before 1960 (column 2) how would you program this in C++.
    Thanks
    Michael

  6. #6
    Registered User
    Join Date
    Feb 2003
    Posts
    28

    excel

    Can you please say what the main instructions in your code do ?
    thanks.
    Michael

  7. #7
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,793

    Re: excel

    Originally posted by m.albert
    A wildcard to look for parts of a word in a cell: for example if I have a worksheet with column 1 (name) 2 (year of birth) and I want to know hw many lines there are where there is Joan or Joanne (so using a wild card Joan*) in comumn 1 and who are born before 1960 (column 2) how would you program this in C++.
    Thanks
    Michael
    To search the spreadsheet I would use the automation functions that excel offers......

    Best bet.....open Excel, Press Alt-F11 to bring up the VB Editor...then F1 to bring up help....on that help, all of Excel's object model is detailed.....those functions are what you want

  8. #8
    Registered User
    Join Date
    Feb 2003
    Posts
    28

    excel

    OK thanks. This is helpful. Actually as I am learning c++ I would prefer to use this language.
    Michael

  9. #9
    Registered User
    Join Date
    Feb 2003
    Posts
    28

    excel questions

    I would also write some programs in c++ to do some statistics on data coming from an excel table.
    CAn you please indicate the instruction to take data in cells from an excel worksheet and enter them say in a c++ multiple array to be analyzed.
    Thanks
    Michael

  10. #10
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,793

    Re: excel questions

    Originally posted by m.albert
    I would also write some programs in c++ to do some statistics on data coming from an excel table.
    CAn you please indicate the instruction to take data in cells from an excel worksheet and enter them say in a c++ multiple array to be analyzed.
    Thanks
    Michael

    Easiest way to extract values?

    Code:
    		_bstr_t bRet;
    
    		bRet = lpWkst->Range["A2"]->GetValue();
    
    		std::cout << static_cast<char*>(bRet);
    But I cant teach you the Excel object model.......thet yo are going to have to do for yourself.....so read the help on excel and go to the sites I originally suggested....I got all my info that way

Popular pages Recent additions subscribe to a feed