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
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
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; }
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
For automation in VC++ there isnt much....best bet is sites like MSDN and Codeguru.comOriginally 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.
Yeah, sure....my example runs with a consoleOriginally posted by m.albert
Can this work as a console c++ separate program ?
Er....in what context?.....I dont understandOriginally posted by m.albert
Does the wildcard* exist in c++ and can it be used here?
Thanks
Michael
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
Can you please say what the main instructions in your code do ?
thanks.
Michael
To search the spreadsheet I would use the automation functions that excel offers......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
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
OK thanks. This is helpful. Actually as I am learning c++ I would prefer to use this language.
Michael
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
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?
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 wayCode:_bstr_t bRet; bRet = lpWkst->Range["A2"]->GetValue(); std::cout << static_cast<char*>(bRet);