Basics of Excel interop

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin - 08 Jun 2007 22:06 GMT

Hello, I'm writing a C# application in VS 2005 that needs to open
Excel and output charts for various reports. My plan of attack was to
record a macro in Excel of the end user creating and formatting the
charts the way they want, and then include the code from the macro in
my C# project.

I added the Microsoft Excel 11.0 Object Library reference (the 2003
PIA) to my project. My major frustration is that the Excel object
model in my C# project is very different from the object model of
Excel istelf - C:\Program Files\Microsoft Office
\OFFICE11\1033\VBAXL10.CHM. And worse, I can't find any documentation
for the PIA's object model!

For example, the macro has the code:

ActiveChart.HasAxis(xlCategory, xlPrimary) = False

But I can't find a HasAxis property anywhere in the PIA's object
model. That's just an example of one thing I haven't figured out
yet... almost every other line of code has required some manipulation
to get it to be like the VBA code from the macro.

Is my plan of mimicking a macro the right way to go about it? Also,
if anyone knows how to find the object model for the PIA, that would
be extremely helpful.

Thanks!
Kevin

Reply to this Message

Kevin - 08 Jun 2007 22:11 GMT

Oh, and I forgot to add that HasAxis is not a property of
Excel._Chart, Excel.Chart, or Excel.ChartClass. At a quick glance,
those seem to have all the same properties and methods -- has anyone
found documentation on why there are three different ones and what
they are for?

I'm trying to pull as much of my code from Microsoft examples as
possible. They use "Sheets" and "_Worksheet" in the example I saw --
I wonder why they choose to use the underscore Worksheet as opposed to
just "Worksheet"?

Excel.Sheets oSheets;
Excel._Worksheet oSheet;

Reply to this Message

Kevin - 08 Jun 2007 23:13 GMT

I just keep coming up with questions.. The macro code has the
following:

ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False

This creates several questions for me -- the first is
SeriesCollection(1) returns an object, and even if you cast it to the
type SeriesCollection there is no Select() method to set the Selection
object to it. Even if there was, I haven't found a "Selection" object
in the PIA object model, so I can't tell where you would even find the
Border property, the Shadow property, etc.

If anyone could help with any of these, that would be fantastic.
Thanks!
Kevin

Reply to this Message

GS - 16 Jun 2007 18:50 GMT

you will have to use range.select
e.g.
Microsoft.Office.Interop.Excel.Range oRange;
oRange = oSheet.get_Range("A1", "A1"); // put your own range here
oRange .Select;

or try casting. good luck

BTW
would you know
1 the range name one get from, Control Home, shiftCONtrol End
2 how to move the cursor or selection using simulated key press on the
active sheet

> I just keep coming up with questions.. The macro code has the
> following:
[quoted text clipped - 4 lines]
> .LineStyle = xlAutomatic
> End With

or try casting

> Selection.Shadow = False
> Selection.InvertIfNegative = False
[quoted text clipped - 9 lines]
> Thanks!
> Kevin

Reply to this Message

Kevin - 18 Jun 2007 15:57 GMT

Thanks for the replies. For your questions, I'm not sure if this is
how you do it, but I record a macro doing what I want, then try to
convert the VBA code into my C# project.

This may not be what you want, but to select the entire sheet my macro
code was:

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Or that the range would be
Range("A1:IV65536").Select

I could not find a way to move the cursor like you were pressing
keys. Only by doing
Range("F13").Select
maybe for each cell?

Kevin

On Jun 16, 1:50 pm, "GS" <gsmsnews.microsoft.co...@msnews.Nomail.com>
wrote:

> you will have to use range.select
> e.g.
[quoted text clipped - 34 lines]
>
> - Show quoted text -

Reply to this Message

GS - 18 Jun 2007 22:37 GMT

excellent, thank you.

btw before I saw your answer, I found the oSheet.UsedRange and seem to
suffice
Your answer can be useful for some other projects where UsedRange will not
cut it

thank you again.

> Thanks for the replies. For your questions, I'm not sure if this is
> how you do it, but I record a macro doing what I want, then try to
[quoted text clipped - 58 lines]
> >
> > - Show quoted text -

Reply to this Message

Ben Voigt [C++ MVP] - 09 Jun 2007 18:45 GMT

> Oh, and I forgot to add that HasAxis is not a property of
> Excel._Chart, Excel.Chart, or Excel.ChartClass. At a quick glance,
> those seem to have all the same properties and methods -- has anyone
> found documentation on why there are three different ones and what
> they are for?

COM/OLE Automation defines interfaces and classes. I suspect you are seeing
a dual interface (dispinterface and v-table interface) and coclass, which
explains the three different names. Usually the interfaces are marked as
hidden in the type library though.

Since the coclass implements the interface, it will have all the same
members as that interface.

> I'm trying to pull as much of my code from Microsoft examples as
> possible. They use "Sheets" and "_Worksheet" in the example I saw --
[quoted text clipped - 3 lines]
> Excel.Sheets oSheets;
> Excel._Worksheet oSheet;

Reply to this Message

Basics of Excel interop

Free Magazines