Getting Excel sheet names

gtag

Hi, I am using following imports: //Mircorsoft Office Objects #import \ "C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\mso.dll"\ rename("DocumentProperties", "DocumentPropertiesXL")\ rename("RGB", "RBGXL") //Microsoft VBA Objects #import \ "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\vbe6ext.olb" //Excel Application Objects using namespace Office; #import "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE"\ rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL")\ rename("DocumentProperties", "DocumentPropertiesXL")\ rename("ReplaceText", "ReplaceTextXL")\ rename("CopyFile", "CopyFileXL")\ exclude("IFont", "IPicture") no_dual_interfaces Excel::_ApplicationPtr XL; Excel::_WorkbookPtr book; Excel::_WorksheetPtr sheet; By using for loop ,able to get all workbook names.Using following code iterating through item, getting all workbook names. book = XL->Workbooks->Item[1]; In case of worksheet, i am not getting the name of worksheet.There is no such option as workbook. Able to point to particular sheet, but name option is not there. sheet = XL->Workbooks->Item[1]->Sheets->Item[1]; I want to retrieve whole list of worksheets present in workbook. Googled but I didn't get any clue. If there is any option, please give me clue. Thanks in advance. Gtag.

Stuart Dootson

gtag wrote:

Able to point to particular sheet, but name option is not there

Yes it is. This code compiles and builds and runs:

// xl is an initialised Excel::_ApplicationPtr variable.
Excel::_WorkbookPtr wb = xl->Workbooks->Add();
Excel::_WorksheetPtr ws = wb->Worksheets->Item[1];
if (ws)
std::cout << ws->Name << std::endl;

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

gtag

Hi Stuart, Thanks again. But I am not getting name its failing at getting sheets from the workbook to which I am pointing. Main aim is to get the workbook names and worksheets names opened by the user, might be 50 workbooks or any number. Please check the code and let me know where I am doing wrong? //MicroSoft Office Objects #import \ "C:\Program Files\Common Files\Microsoft Shared\OFFICE11\mso.dll" \ rename("DocumentProperties", "DocumentPropertiesXL") \ rename("RGB", "RBGXL") //Microsoft VBA Objects #import \ "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\vbe6ext.olb" using namespace Office; //Excel Application Objects #import "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" \ rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL") \ rename("DocumentProperties", "DocumentPropertiesXL") \ rename("ReplaceText", "ReplaceTextXL") \ rename("CopyFile", "CopyFileXL") \ exclude("IFont", "IPicture") no_dual_interfaces using namespace std; #include <string> int _tmain() { Excel::_ApplicationPtr XL; Excel::WorkbooksPtr book; Excel::WorksheetsPtr sheet; string BookName; string SheetName; int BookCount; int SheetCount; string bookArray[20]; int count; //A try block is used to trap any errors in communication try { //Initialise COM interface CoInitialize(NULL); HRESULT hr = XL.GetActiveObject(L"Excel.Application"); if(SUCCEEDED(hr)) { book = XL->Workbooks; BookCount = book->Count; for(int i=1;i<=BookCount;i++) { BookName = book->Item[i]->Name; cout<<BookName<<endl; SheetCount = book->Item[i]->Sheets->Count; for(int j=1;j<SheetCount;j++) { sheet = book->Item[i]->GetWorksheets(); if(sheet) { Excel::_

Stuart Dootson

gtag wrote:

Excel::WorksheetsPtr sheet;

Should be Excel::SheetsPtr sheet;

gtag wrote:

Excel::_WorksheetPtr pSheet = sheet->Item[j];

Not all sheets are worksheets. This line can raise an exception as well if, for example, you have a chart sheet. So, you should use this code:

      sheet = book->Item\[i\]->Sheets;
      if(sheet)
      {
           try {
              Excel::\_WorksheetPtr pSheet = sheet->Item\[j\];
              SheetName = pSheet->Name; 
              cout<<SheetName<<endl;
           }
           catch (\_com\_error&) { cout << "Sheet "  << j << " is not a worksheet\\n"; }
      }

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

gtag

Hi stuart, Thanks again. I corrected, its working perfectly. One more help I need, I just want to know , is there any documentation on excel for programming in C++ , I have checked msdn, codeproject, there are only basic things related to excel not at events level. If there are any, please provide me the link where I get the info. Or if you have any such stuff on excel(writing excel events etc), please let me know. Regards, Gtag

Stuart Dootson

I just use the help that comes with Excel (go into the VBA IDE by pressing Alt-F11 when in Excel and you can get help on the Excel object model). If (as you're doing) you use #import, use of the Excel object model in C++ follows on pretty straight forwardly from using it in VBA, so you might be best off prototyping in VBA first, then moving to C++ once you know what you want to do. I don't know of any links to Excel programming in C++. What I've picked up has been a) from knowing the Excel object model (the VBA experience!), and b) knowing how to create a connection point handler (see this post of mine[^] for a complete example of an Excel event handler). It's really not difficult given those two things.

Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p

Lost User

Thanks, brilliant answers; why did I never think of it? Will save me a load of time with a project I'm working on.

gtag

Hi, I checked your post in C/C++ related to events. Its really helpful. Thank you once again. Gtag

gtag

Hi stuart, I have one more issue. My application works fine for Office 2007. For office 2003, AfterCalculate is not triggering. Googled, in msdn got the clue that it is new in 2007 and its not available in 2003. If you have any idea, please let me know. Thanks in advance. Regards, gtag