.Left positioning shapes in wrong place in PowerPoint 2016 VBA

1
votes

I'm trying to copy ranges from an Excel sheet and paste them into slides as tables, then position and resize them. However, when I try to position the shapes in ppt, the .Left method doesn't behave as expected; in the slide thumbnails on the left of the screen the shapes are in the expected positions (centred as in the below code), but when I select the slide the shapes are shifted far to the right of where they ought to be.

I thought it may be using 'centre' as its reference point instead of the left bound of the slide but this doesn't match up with the amount it's offset.

Would really appreciate some help with this - would be good to know if it's a bug in ppt or an error in my code as if it's a bug it has a large impact on the feasibility of this project. For this particular example I could probably use the .Align method as a workaround but for later slides I need to be able to position multiple shapes on the same slide accurately.

I'm using Office 365.

Code below:

Sub PP_export()

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim XLws As Worksheet

Set PPApp = New PowerPoint.Application
Set XLws = ActiveSheet
Set PPPres = PPApp.Presentations.Open("Y:\Research\PROJECTS\2018\Magic Macro\ppt_template_.potx")
PPApp.Visible = True

''Lifestyle Statements
'By Col%
Set PPSlide = PPPres.Slides(3)
Dim LSCol As PowerPoint.Shape

XLws.Range("M106:o126").Copy
PPSlide.Shapes.PasteSpecial ppPasteDefault

Set LSCol = PPSlide.Shapes("Table 2")
With LSCol
    .Left = (28.35 * 10.56)
    .Top = (28.35 * 3.83)
    .Height = (28.35 * 13.21)
    .Width = (28.35 * 12.75)
End With

'By Index
Set PPSlide = PPPres.Slides(4)
Dim LSIndex As PowerPoint.Shape

XLws.Range("Q106:s126").Copy
PPSlide.Shapes.PasteSpecial ppPasteDefault

Set LSIndex = PPSlide.Shapes("Table 2")
With LSIndex
    .Left = (28.35 * 10.56)
    .Top = (28.35 * 3.83)
    .Height = (28.35 * 13.21)
    .Width = (28.35 * 12.75)
End With
2
vbaexceloffice365powerpoint
Welcome to Stack Overflow! Have you checked the method's documentation? Also, what/why are you multiplying? Far left is .Left=0. – ashleedawg
Thanks! Yes - Not many clues unfortunately. .Left is a property of shape objects that simply represents the distance in points from the left edge of the shape's bounding box to the left edge of the slide. It can either be set with an integer value or return the integer value of a shape's leftmost position. I'm multiplying as VBA measures in points but I have cm values of where I'd like the position to be and the conversion factor is 28.35 – kaydee
Without seeing what you have I can't give much help (or visualize your issue is) but can you place these shapes properly manually? If so, what is the value of .Left after you do so? – ashleedawg
See if pasting as ppPasteEnhancedMetafile helps. – CCM
@CCM that does seem to fix the positioning issue, but pastes the range as an image rather than a table which isn't ideal. If it's the only way to get round it then I can format in Excel and paste as an image but ideally people would be able to copy and paste text from these tables. – kaydee

2 Answers

2
votes

I got the same problem and I run a debug message showing the position after inserting and it seems to be correct. But only the thumbnail looks fine, the actual slide is wrong.

I found out that the position of the table will be correct when activating or displaying the slide before pasting the table. Also if several tables need to be inserted I needed to make a short break of 2 seconds between the paste operations.

My workaround is pretty ugly and I am still searching for the reason. It is likely a bug but maybe it also has to do with some kind of offset of the slide in the background.

0
votes

You need to loop over the table and the data if you are using a template. If you already have a table (table 2) on slide 3, then you could use this. just send in the excel file object and the template (path+name) it may need some tinkering.

Sub Slide_3(ByRef xlWorkBook, Template)

For Each oSh In Presentations(Template).Slides(3).Shapes
        Select Case oSh.Name
            Case "Table 2"
            For i = 1 To 20
                For j = 1 To 3
                    oSh.Table.Cell(i, j).Shape.TextFrame.TextRange.Text = xlWorkBook.Worksheets(24).Cells(105 + i, j + 12).Value
                Next j
            Next i
 'M106:o126

         End Select

Next oSh

End Sub