Consulting

Results 1 to 16 of 16

Thread: Shape handling problem

  1. #1

    Shape handling problem

    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

  2. #2
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    Whats the purpose of this check?
    [vba]
    For Each pptShape In pptApp.ActiveWindow.Selection.SlideRange.Shapes
    If pptSlide.Shapes.Range(pptShape.Name).Name = oname Then[/vba]
    There are several way to identify the pasted shape

    It will always be the top shape

    [vba].Shapes(pptslide.shapes.count)[/vba]

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

    [vba]With .Shapes.PasteSpecial ppPasteShape
    .Name="myunique name"
    end with[/vba]

    OR

    [vba]set nowshp=.Shapes.PasteSpecial ppPasteShape[/vba]

    (NOTE none of this is tested! But is should work!)
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  3. #3
    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

  4. #4
    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?

  5. #5
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    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
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  6. #6
    John,

    I removed the ppPasteShape from here [VBA] .Shapes.PasteSpecial ppPasteShape[/VBA] 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 [VBA]
    For Each pptSlide In pptPres.Slides
    For Each pptShape In pptSlide.Shapes If pptShape.Name = oname Then
    With pptSlide.Shapes.PasteSpecial
    [/VBA]

    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

  7. #7
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    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

    [vba]PPTplc.Select
    ActiveWindow.View.Paste[/vba]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  8. #8
    John,

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

    Cheers,
    S.

  9. #9
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    I meant something like this:

    [VBA]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[/VBA]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  10. #10
    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?

  11. #11
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    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
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  12. #12
    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.

  13. #13
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    Got you! Obviously you will need to adapt but something vased on this maybe:

    [VBA]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[/VBA]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

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

    best,
    S.

  15. #15
    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.

    [VBA]pptApp.ActivePresentation.Slides.Range.Shapes.Range(pptShape.Name).Select[/VBA]

    any thoughts?

    Cheers,
    S.

  16. #16
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,093
    Location
    There is hardly ever a need to select shapes in PPT. If you explain EXACTLY what you are trying to achieve ....
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •