Excel Automation in C++/Cli

[Bill Below]

I'm making my first try at C++/Cli coding by compiling an existing C++ app in
VS2005 with /clr and changing the code that starts and runs Excel to use the
Excel 2003 PIA. With VS2003, I used Add Class to generate header files for
the Excel classes. Add Class in VS 2005 uses the #import directive to
generate humongous thi and tlh files that don't compile. That's why I am
trying the managed approach.

I am down to just a few compile errors and they involve properties of Range.
The old code was of the form:

CRange oRange = .... ;
oRange.put_Value(COleVariant(saValues));

where saValues is a SafeArray.

Now I need something of the form:

Range^ hRange = .....;
hRange->Value = ??????

and I am stuck.

Anyone know of a C++/Cli sample that applies to this? Is there more
specific documentaion than the Excel PIA Reference which tells me to pass an
Object? Are SafeArrays still used in the managed world?

Thanks,
Bill

 

[Bob H]

Not a complete answer, but I noted this in going back and looking at some of
the references I had been looking into some time ago. From
http://support.microsoft.com/default.aspx?scid=kb;en-us;q311452 :
2. The death of the variant The .NET-managed languages use common data
type system in the common language runtime. The important thing about this
type system is that all data types inherit from a single "object" type which
permits data to be handled polymorphically without the need for another
outside type (for example, the COM or OLE Variant). Therefore, the .NET
Framework does not use Variants. If you port Office code from Microsoft
Visual Basic for Applications (VBA) or Visual Basic 6.0, you must retype
Variants as Objects, and the new Object type can contain other data types
than just Automation (IDispatch*) objects. Pay close attention to how your
code uses these types, and add type checking as necessary.


I have done only a little automation of Office from VS - though a lot
directly from add-ins I create from within Office using VBA. What little I
have done automating office from VS.Net was in VB. So, I hadn't actually
had to deal with the exact situation you ran into due to the
dis-similarities between the two languages in interfacing with Office. When
attempting to set a cell value, at least in VB(.Net), I found that the
conversion for strings (at very least) implicitly converted without extra
effort applied to the rvalue. It appears, that (from the above excerpt)
that you would have more to do in reading a cell value. Since I noted that
no one else had replied as of yet, I thought that at least this info and
link might give you a clue or a trail to follow. Good luck.

Bob H

 

[Bill Below]

Thanks, Bob

It did help. The "Death of Variant" KB led me to a C# KB that showed how
the Excel PIA accepts managed arrays in place of SafeArrays. I got rid of my
SafeArrays and set up managed arrays of double and String^.

Now I can pass some data to Excel as follows:

array<String^, 2>^ strarray = gcnew array<String^>(x, y);
Range^ hRange = .......
hRange->NumberFormat = strarray;

This not only compiles but it works.

I am still stuck, however, when it comes to setting the Range Value property.

array<double, 2>^ vals = gcnew array<double, 2>(x, y);
hRange->Value = vals;

gives me:
error C2660: 'Microsoft::Office::Interop::Excel::Range::Value::set' :
function does not take 1 arguments

The C# example suggests that two arguments must be passed to the Value
property but after a lot of attempts I have not been able do this in C++.

Thanks,
Bill

 

[Bill Below]

The answer is:

hRange->Value[nullptr] = vals;

which translates to accessor arguments:

set(nullptr, vals);

 

[Bob H]

Glad that the first tid-bit got you on to a path that ended up working.
Sorry that I only got back to check on this thread now, but couldn't have
answered your question directly anyways. If I were to respond I would have
backed down to just setting a cell value and then move on to the setting an
entire range only after setting the single cell value was working. I have
done a ton of C code in my day, but I only dabble in Microsoft C++. So,
when I read your last question, there were already a few points I'd have to
refresh on before getting to how the data is marshaled by the interop. All
said, thanks for posting the answer, to your own question. I may print this
one and save it to look over again later. Good luck.

BobH

The answer is:

hRange->Value[nullptr] = vals;

which translates to accessor arguments:

set(nullptr, vals);

Thanks, Bob

It did help. The "Death of Variant" KB led me to a C# KB that showed how
the Excel PIA accepts managed arrays in place of SafeArrays. I got rid
of my
SafeArrays and set up managed arrays of double and String^.

Now I can pass some data to Excel as follows:

array<String^, 2>^ strarray = gcnew array<String^>(x, y);
Range^ hRange = .......
hRange->NumberFormat = strarray;

This not only compiles but it works.

I am still stuck, however, when it comes to setting the Range Value
property.

array<double, 2>^ vals = gcnew array<double, 2>(x, y);
hRange->Value = vals;

gives me:
error C2660: 'Microsoft::Office::Interop::Excel::Range::Value::set' :
function does not take 1 arguments

The C# example suggests that two arguments must be passed to the Value
property but after a lot of attempts I have not been able do this in C++.

Thanks,
Bill