How to read late bound Excel worksheets collection properties?

[J Daniel Melton]

Hello,

I am using late binding in a managed VC++ .NET 2003 application. I used KB
302902 (for C#) as a starting point and converted it to managed C++. I built
a managed class that is intantiated by a form. The class can instantiate
Excel, open a new workbook, add a new worksheet, get a range, and put some
data in it. So, I have basically just duplicated what KB 302902 does, except
I am working out of a managed C++ class.

The problem comes when I wish to open an existing workbook and find what
worksheets are in it.

In the first read attempt, the form used an OpenFileDialog to let the user
select which workbook to open. The class successfully instantiated Excel,
added the workbook received from the OpenFileDialog to the workbooks
collection, and made Excel visible, using late binding. Good.

In the second read attempt, the class gets the worksheets collecton:

// Declarations in the class header file.
System::Object * objBook_Late;
System::Object * objSheets_Late;

// Get the worksheets collection.
objSheets_Late = objBook_Late->GetType()->InvokeMember( "Worksheets",
System::Reflection::BindingFlags::GetProperty,
NULL, objBook_Late, NULL );

I think the above is OK. One of the worksheets collection properties is
Count. What I wish to do next is get the count, then iterate through the
z-list and record the worksheet names in an array. (As you can see, I did
not change the KB 302902 object names...I am just learning here.)

Since there is an object objSheets_Late, I tried to use it to get the count.
I used both GetProperty and GetField for BindingFlags. I could get a return
into another object with GetProperty, yet I still am not sure how to find
the count in the new object.

Can someone point me to an information source on how to extract the
properties from a late bound object such as objSheets_Late? Two things in
particular. 1. Extract a value such as Count. 2. Iterate through the z-list
to extract the corresponding worksheet names.

Thanks,

Dan

 

[J Daniel Melton]

I have answered my question.

// Declarations in the class header file.
System::Object * worksheetCount;
System::String * worksheetNames[];

// Get the count of worksheets in the workbook.
worksheetCount = new Object;
worksheetCount = objSheets_Late->GetType()->InvokeMember( "Count",
System::Reflection::BindingFlags::GetProperty, NULL,
objSheets_Late, NULL );
// Store the count of worksheets in a local variable.
// To do this, the count must be 'unboxed' via a dynamic cast.
// The source for how to do this was 'Microsoft Visual C++ .NET Step by
Step: Version 2003',
// Chapter 25, in the section 'Unboxing'.
int wsCount = *dynamic_cast<__box int*>(worksheetCount);
// Get the names of the worksheets. The worksheet count starts with 1.
this->worksheetNames = new System::String * [wsCount+1];
System::Object * wsName;
for(int i = 1; i <= wsCount; i++)
{
Parameters = new Object * [1];
Parameters[0] = __box(i);
objSheet_Late = objSheets_Late->GetType()->InvokeMember( "Item",
System::Reflection::BindingFlags::GetProperty, NULL,
objSheets_Late, Parameters );
wsName = new Object;
wsName = objSheet_Late->GetType()->InvokeMember( "Name",
System::Reflection::BindingFlags::GetProperty, NULL,
objSheet_Late, NULL );
// Store the name.
System::String * wsTemp = dynamic_cast<System::String*>(wsName);
this->worksheetNames = wsTemp;
}

Now I am able to access the range object on any available worksheet as
needed.

The problem I had was with understanding how to use the C++, not with the
Excel object model.