Shape handling problem

[Sylvia2012]

Hey guys,

After a few tries of my own I thought I should seek enlightment from VB gurus.

I am building an application in VB which purpose is to copy designated charts from Excel and paste them to a PowerPoint presentation. The code is on Excel VB Editor but I do not have a problem to split it and run a separate part on PPT.

I have realised a rough version but it needs some finetunning. What I have done so far is to initiate a the new PPT object, browse through excel's chart objects and copy the appropriate ones and paste them to PPT slide.

My problem is that while i know how to perform the copy-paste routine and I have managed to paste the charts in a designated slide, after the pasting ends I cannot find a way to slect those pasted charts and reposition them as I want because certain slides have 2 or more pasted charts and I cannot find a way to select them and reposition them to the appropriate position within the slide. Only way I can think of it is to select them by coordinates but I would use this solution as a last resorts.

Please share thoughts or suggestions, they are more than welcome.
To skip a future post i have included my code:

Sub test1()
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape
Dim i, SlideNum As Integer, strString As String
Dim limit, pathn, objcount, countTables, limitt As Integer
Dim chtobj As ChartObject
Dim ws As Worksheet



Set pptApp = CreateObject("PowerPoint.Application")
pptApp.Visible = True
Set pptPres = pptApp.Presentations.Open("C:\Documents and Settings\Sylvia\Desktop\test\test.ppt")

ActiveWorkbook.Activate

For Each ws In ActiveWorkbook.Worksheets

For Each chtobj In Sheets(ws.Name).ChartObjects
oname = chtobj.Name
If InStr(1, chtobj.Name, "BNChart", vbTextCompare) Then
Sheets(ws.Name).Activate
ActiveSheet.ChartObjects(oname).Activate
ActiveChart.CopyPicture Appearance:=xlScreen, _Size:=xlScreen, Format:=xlPicture

For Each obj In pptPres.Slides

With pptPres.Slides
Set pptSlide = pptPres.Slides(obj.Name)
End With

With pptSlide

For Each pptShape In pptApp.ActiveWindow.Selection.SlideRange.Shapes
If pptSlide.Shapes.Range(pptShape.Name).Name = oname Then
.Shapes.PasteSpecial ppPasteShape
GoTo Holder
End If

Next
End With
Next
End If
Holder: Next
Next

End Sub


Cheers,
Sylvia

[John Wilson]

Whats the purpose of this check?

For Each pptShape In pptApp.ActiveWindow.Selection.SlideRange.Shapes
If pptSlide.Shapes.Range(pptShape.Name).Name = oname Then
There are several way to identify the pasted shape

It will always be the top shape

.Shapes(pptslide.shapes.count)

You could set an object var when you Paste or give it a unique name

With .Shapes.PasteSpecial ppPasteShape
.Name="myunique name"
end with

OR

set nowshp=.Shapes.PasteSpecial ppPasteShape

(NOTE none of this is tested! But is should work!)

[Sylvia2012]

Dear John,

Thank you for your immediate response, I am using this for loop for the following reason: The PPT template I am using has certain named shapes which I am using to identify the target slide for the chart to be pasted to.
So if I have lets say Chart_1 copied from excel I switch to the PPT presentation and I browse through its slide and through each shape to find the shape named Chart_1, and when this happens the chart is pasted there but not in the position I want. So, the problem is how can I reselect it and reposition it. I have tried to browse through shapes again but it is not recognised as one.
I will check your suggestions and let you know.

Cheers,
Sylvia

[Sylvia2012]

John,

I tried to assign a name as you suggested and it worked, thing is now when I try to manipulate it in terms of position I get an errorof method.

I should let you know that when I try to browse through the shape collection, my newly pasted chart is not a part of it for some reason.

I suspect the reason has to do with the data format of the object copied-pasted, like this part of code:
ActiveChart.CopyPicture Appearance:=xlScreen, _Size:=xlScreen, Format:=xlPicture

any ideas?

[John Wilson]

Is the shape physically on the slide?
If you are copying the chart as a picture you should be able to paste instead of paste special.

cant see any obvious problem with the code though

[Sylvia2012]

John,

I removed the ppPasteShape from here .Shapes.PasteSpecial ppPasteShape and it worked as you said. I will try it out and let you know of the outcome.

By the way, since you seem like you know your way around VBA i wanted to ask you this: I am using this bit of code here to match the chart with the appropriate slide

For Each pptSlide In pptPres.Slides
For Each pptShape In pptSlide.Shapes If pptShape.Name = oname Then
With pptSlide.Shapes.PasteSpecial


I have named several textboxes on each slide with the same name as the object being copied and i am trying to match them to determine the paste location. Is there a way to paste to a specific placeholder and/or copy-paste its position settings to the newly pasted chart?

thanks

[John Wilson]

It's not at all obvious how to paste into a placeholder!

This way can often work, try it out.

Find the placeholder (assuming here you have set a reference to eg PPTplc)
Select it
Then Paste to the ActiveWindow.View

PPTplc.Select
ActiveWindow.View.Paste

[Sylvia2012]

John,

i am trying your idea, can you elaborate a bit more on the steps you described above?

Cheers,
S.

[John Wilson]

I meant something like this:

For Each pptshape In pptslide.Shapes
If pptshape.Name = oname Then
If pptshape.Type = 14 Then 'placeholder
pptshape.Select
ActiveWindow.View.Paste
End If
End If
Next pptshape

[Sylvia2012]

John,
i do not know if I am doing something wrong but I cannot get the result i hoped for as It doesn't paste at the position of the placeholder.
Is there a way to extract the placeholders position so I can use it as guide?

[John Wilson]

It will if:
1, The placeholder can contain an image (ie NOT a title/chart/table placeholder etc) Title is most likely.

Once you have checked it is a Plaeholder you can check the Shape.PlaceholderFormat.Type

2. The placeholder is empty

[Sylvia2012]

Indeed, but I think that the placeholder is taking the size of the image to be pasted rather than the inverse, which is what i would like to achive.

[John Wilson]

Got you! Obviously you will need to adapt but something vased on this maybe:

Sub plcholder()
Dim pptplc As Shape
Dim osld As Slide
Dim sngL As Single
Dim sngT As Single
Dim sngW As Single
Dim sngH As Single
Set osld = ActivePresentation.Slides(1)
For Each pptplc In osld.Shapes
If pptplc.Type = 14 Then
If pptplc.PlaceholderFormat.Type = 7 Then
With pptplc
sngL = .Left
sngT = .Top
sngH = .Height
sngW = .Width
pptplc.Select
ActiveWindow.View.Paste
With osld.Shapes(osld.Shapes.Count)
.LockAspectRatio = False
.Height = sngH
.Width = sngW
.Left = sngL
.Top = sngT
End With
End With
End If
End If
Next
End Sub

[Sylvia2012]

John,
perfect timing! That was exactly what I started working on.
Seems like the best (closer) approach of my problem.

best,
S.

[Sylvia2012]

Hey guys,

quick question, I just migrated to Office 10, the code that ran smoothly on Excel 2003 now gives some errors Run-time error 2147188160 (80048240) Slide(unknown member) object does not exist on this line of code.

pptApp.ActivePresentation.Slides.Range.Shapes.Range(pptShape.Name).Select

any thoughts?

Cheers,
S.

[John Wilson]

There is hardly ever a need to select shapes in PPT. If you explain EXACTLY what you are trying to achieve ....