[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.interop

Basics of Excel interop

Kevin

6/8/2007 9:06:00 PM

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
6 Answers

Kevin

6/8/2007 9:12:00 PM

0

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;

Kevin

6/8/2007 10:13:00 PM

0

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

Ben Voigt [C++ MVP]

6/9/2007 5:46:00 PM

0


"Kevin" <JunkMailOnly95@yahoo.com> wrote in message
news:1181337119.500712.204060@p47g2000hsd.googlegroups.com...
> 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 --
> I wonder why they choose to use the underscore Worksheet as opposed to
> just "Worksheet"?
>
> Excel.Sheets oSheets;
> Excel._Worksheet oSheet;
>

GS

6/16/2007 5:51:00 PM

0

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

"Kevin" <JunkMailOnly95@yahoo.com> wrote in message
news:1181340790.373677.241310@q75g2000hsh.googlegroups.com...
> 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
or try casting
> 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
>

Kevin

6/18/2007 2:57:00 PM

0

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.
> 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
>
> "Kevin" <JunkMailOnl...@yahoo.com> wrote in message
>
> news:1181340790.373677.241310@q75g2000hsh.googlegroups.com...
>
>
>
> > 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
> or try casting
> > 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- Hide quoted text -
>
> - Show quoted text -

GS

6/18/2007 9:37:00 PM

0

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.

"Kevin" <JunkMailOnly95@yahoo.com> wrote in message
news:1182178629.668050.257940@w5g2000hsg.googlegroups.com...
> 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.
> > 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
> >
> > "Kevin" <JunkMailOnl...@yahoo.com> wrote in message
> >
> > news:1181340790.373677.241310@q75g2000hsh.googlegroups.com...
> >
> >
> >
> > > 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
> > or try casting
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
>