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

 

[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]...

 

[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

 

[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