Existing OLE Object get resized/dislocated on pasting new OLE object

[Hari Prasadh]

Hi,

I have a PPT file with a single slide in it. It has one Excel OLE chart as a
bar graph displaying data for present year.

I want to paste another excel sheet (one row and lets say 30 columns) below
this chart such that x-axis value labels align themselves with 2004's
values.

I paste this excel worksheet data in this manner: -
a) Go to excel and copy one row by 31 columns.
b) Activate powerpoint and go to the slide and click on the area near the
bottom of the existing excel chart OLE. (Due to this the chart gets
activated)
c) Paste the data using paste special - excel worksheet


Result - The position of my previous excel ole chart gets changed and it
also gets beomes much smaller and I play a game of cat and mouse with my
mice to set things in order, but alas Im never able to get it look like
before pasting. Where am I going wrong?

I tried with RnR starter set where before pasting the worksheet I memorize
the position of excel chart and then paste my excel worksheet then select my
excel chart and then Hammer the previously remembered position. but RnR is
not able to do what I wanted. RnR is not able to make BOTH the position and
scale (height, top, width and left ) same.

Please guide me.

Thanks a lot,
Hari
India

 

[Steve Rindsberg]

I tried with RnR starter set where before pasting the worksheet I memorize
the position of excel chart and then paste my excel worksheet then select my
excel chart and then Hammer the previously remembered position. but RnR is
not able to do what I wanted. RnR is not able to make BOTH the position and
scale (height, top, width and left ) same.

By default, the Hammer tool is set never to distort shapes, but you can change
that. Ctrl+Click it to change the settings; put a check next to Resize and
remove the check next to Don't Distort.

It will then set both the height and width.

That still may not do the job you want it to; embedded Excel content in PPT
has a habit of resizing itself when it's activated. It sounds as though this
is what you're running into.

 

[Hari Prasadh]

Hi Steve,

Thnx a lot for your reply.

I have a related problem now. Iam trying to do the same data updation using
a macro written in Excel to update an OLE (excel Chartsheet) in PPT.

When I do it then the data gets updated but the view in OLE PPT changes from
chart to Excel wksheet (which contains the data for the chartsheet) and also
the whole excel workbook gets shrunken.

Is there a way out for me? Please help me out. Would be extremely grateful.

Here is the novice macro for the same.

'below macro written run from personal.xls workbook.

Option Explicit
Dim oPPTApp1 As PowerPoint.Application
Dim oPPTShape1 As PowerPoint.Shape
Dim rngNewRange1 As Excel.Range
Dim oExceldata As Object
Dim Excelwksheet As Worksheet
Dim year As String



Sub UpdateExcelData()

year = InputBox("Which Year - 2004 or 2005?")


Set oPPTApp1 = CreateObject("PowerPoint.Application")



oPPTApp1.Visible = msoTrue




With oPPTApp1.ActivePresentation.Slides(1)
For Each oPPTShape1 In .Shapes

If oPPTShape1.Name = "NRMAWC023" And year = "2005" Then



Set oExceldata = oPPTShape1.OLEFormat.Object



Set rngNewRange1 = ActiveSheet.Range("A10:ag13")
rngNewRange1.Select
rngNewRange1.Copy

Set Excelwksheet = oExceldata.Worksheets("q20")
Excelwksheet.Range("A9").PasteSpecial xlPasteValues



ElseIf oPPTShape1.Name = "Object 29" And year = "2004" Then

Set rngNewRange1 = ActiveSheet.Range("B17:ag17")
rngNewRange1.Select
rngNewRange1.Copy

Set Excelwksheet = oExceldata.Worksheets("sheet1")
Excelwksheet.Range("A2").PasteSpecial xlPasteValues
End If


Next oPPTShape1

End With

End Sub


Thanks a lot,
Hari
India

 

[Hari Prasadh]

Hi Steve,

Just wanted to explain my situation in a little more detail.

I have 2 objects in my PPT

One is "NRMAWC023" - It has an excel chartsheet with name "Chart1" and a
worksheet "q20". The chart sheet "chart1" is the one which is displayed in
PPT and the worksheet "q20" contains the data for "chart1".

When I get the inputbox (on running my code in excel) I write 2005 and then
my data in the object NRMAWC023 gets updated but then the active view in
excel OLE changes from "chart1" to "q20". Also, the whole object gets
RESIZED and repositioned.

Thus automation defeats the one of the main purpose of why I want to
automate, which is to avoid "embedded Excel content in PPT has a habit of
resizing itself when it's activated."

Please tell me what code I should write/inlcude in the exiting code so that
the existing object doesnt get resized/repostioned and also the ACTIVE VIEW
remains at "chart1".

Thanks a lot,
Hari
India


news:[email protected]...

 

[Steve Rindsberg]

Hi Steve,

Thnx a lot for your reply.

I have a related problem now. Iam trying to do the same data updation using
a macro written in Excel to update an OLE (excel Chartsheet) in PPT.

When I do it then the data gets updated but the view in OLE PPT changes from
chart to Excel wksheet (which contains the data for the chartsheet) and also
the whole excel workbook gets shrunken.

I've seen similar things; I'm not sure of the cause, but since it happens
independent of VBA code in the cases I've seen, it seems to be something that'd
have to be worked out in other ways. To verify: doubleclick the OLE shape in
PPT to activate it, then click off to deactivate it. Does it change size? Try
activating again and chaning a bit of data. Now does it change size.

 

[Hari Prasadh]

Hi Steve,

I see that way I can overcome this problem is recording the position of Top,
left, Width and Height before pasting new data and then applying these same
positions on the DISLOCATED object after pasting. - this would solve my
problem of dislocation to some extent. (some extent only because this new
method is now causing dislocation of unrelated objects on the same slide?)

Also I think that the solution to the problem may lie in using Lock aspect
ratio or Auto scale feature in object format/graphs options, though not
sure.

Any thoughts?

I found the solution to the problem of view changing from chart to data by
setting a handle to the chart sheet and then doing activate chart sheet and
then using refresh option (or the xlsheet visible = 1).

I thought that activate chart sheet method alone should have worked well.
but it did not? Any better solutions?

Thanks a lot,
Hari
India

 

[Steve Rindsberg]

Hi Steve,

I see that way I can overcome this problem is recording the position of Top,
left, Width and Height before pasting new data and then applying these same
positions on the DISLOCATED object after pasting. - this would solve my
problem of dislocation to some extent. (some extent only because this new
method is now causing dislocation of unrelated objects on the same slide?)

Do you have Automatic Layout for Inserted Objects turned on? That'll cause no
end of trouble. It sounds like that might be the problem.

Also I think that the solution to the problem may lie in using Lock aspect
ratio or Auto scale feature in object format/graphs options, though not
sure.

It's hard to say w/o seeing specific cases; however, if Aspect ratio is locked,
then every time you change the height, the width will change accordingly to
maintain the original aspect ratio. And vice versa, of course. If aspect
ratio is off, then the shape might become distorted; I don't think you'd want
that to happen.

Any thoughts?

I found the solution to the problem of view changing from chart to data by
setting a handle to the chart sheet and then doing activate chart sheet and
then using refresh option (or the xlsheet visible = 1).

I thought that activate chart sheet method alone should have worked well.
but it did not? Any better solutions?

Not offhand, but something similar happens with MSGraph charts. If you don't
call the update method before closing MSGraph, your changes don't get recorded.

 

[Hari Prasadh]

Hi Steve,

Do you have Automatic Layout for Inserted Objects turned on? That'll
cause no.

This is the price one pays for not asking questions if one doesnt understand
the answer. You mentioned "Automatic Layout for inserted objects" about
which I had no idea. It sounded Greek to me and I thought that since I have
used simple things (?) in my PPT so such stuff shouldnt be applicable to me.

Bingo, my colleague was playing around with PPT settings and came across
this feature and after turning it off (unchecking it) in 2003 saw that OLE
objects moved a little lesser as compared to before.

Though in 2002 when i did the same thing it did not help me much.

Again in some other 2003 systems it wasnt working up to expectations.

I know think that this feature can somehow help me if I learn a little
more.

Could you please tell me whether other than turning this off I need to
something else also to make it work?

(And yes, most importantly I already have a template which am filling with
different data sets and creating different PPT's. So, the object in PPT has
already been inserted and am just updating the data behind it
programmatically. So am never inserting objects each time).

Thanks a lot,
Hari
India

 

[Steve Rindsberg]

Hi Steve,

This is the price one pays for not asking questions if one doesnt understand
the answer. You mentioned "Automatic Layout for inserted objects" about
which I had no idea. It sounded Greek to me and I thought that since I have
used simple things (?) in my PPT so such stuff shouldnt be applicable to me.

Bingo, my colleague was playing around with PPT settings and came across
this feature and after turning it off (unchecking it) in 2003 saw that OLE
objects moved a little lesser as compared to before.

Though in 2002 when i did the same thing it did not help me much.

Again in some other 2003 systems it wasnt working up to expectations.

I know think that this feature can somehow help me if I learn a little
more.

I'd think you'd want it OFF. W/o question. You don't want MS deciding where
things belong on the slide for you, and more to the point, you don't want them

Could you please tell me whether other than turning this off I need to
something else also to make it work?

(And yes, most importantly I already have a template which am filling with
different data sets and creating different PPT's. So, the object in PPT has
already been inserted and am just updating the data behind it
programmatically. So am never inserting objects each time).

I don't know the answer to that, I'm afraid; if it were happening here, I'd
try to step through the code, watching the result in PPT at each step to see
where the sizing does something I didn't expect.