Excel worksheet embeded in Powerpoint (VBA)

[Guest]

Hello all,

I'm going nuts over a problem I am having. I have been searching the net for
over a day now and can find no answer.

I am creating a macro which creates a powerpoint slide from a template slide
and data from excel. One part of the slide is an embeded excel worksheet,
displaying nine rows and three columns. After I transfer all the data,
sometimes the excel sheet is bigger than I originally made it (The rows
increase in vertical size due to a wealth of information) and over runs other
data on the slide.

I can shrink the size of the shape object containing the excel sheet, no
problem. But it treats the sheet as a picture and shrink everything. What I
need is to be able to tell the sheet to display only X amount of rows instead
of the original nine. (As if I have edited the object and manually dragged
the bottom border up excluding some rows)

The trick is that I can not find a method or property I can use to do this
in VBA. I can select the object using "OLEFormat.Object" I can even cast it
to an "Excel.Worksheet" object type, but I can not seem to affect the rows or
columns displayed, just the size of the shape used to display them.

All I can find on the net is resizing embeded Charts not worksheets.

Anyone got any ideas?

And yes, I know that if I do not make the rows word wrap my problem goes
away entirely (row height never changes), but I need the rows to wrap, so
that is out.

I am using Office 2003, Excel and Powerpoint, if that information helps.

Thanks,
Vid

 

[ChrisHarrington]

If you only want to show X rows, then why not have the macro perform this
row limiting function?

 

[Guest]

In case I worded it incorrectly. I do not have a function to limit the
visible rows. That is what I'm looking for.

 

[ChrisHarrington]

How about posting the macro as you have it now - it shouldn't be hard to
chang it to do the filtering

 

[Guest]

Alright, this problem is now solved. Well, kind of.

A friend sent me some code showing how he dealt with this problem.

*********
ActiveWindow.Selection.SlideRange.Shapes.("ShapeName").PictureFormat.CropBottom = 50
**********

If you crop the bottom of the picture it reduces the amount of excel being
shown, but does not have the side effect of simply adjusting the Shape's size
(increasing the resolution, thereby shrinking the picture porportionally
instead of cutting it off).

The problem still remians of not cutting a row in half (which this method
does), but that can be solved by some calculations on row height.