CODEWITHSUNDEEP
excelvbapowerpointshapes
Mazt Entropy
Mazt Entropy

Reputation: 11

Pasting multiple shapes in a Powerpoint Slide using VBA-Excel

I have created an Excel macro which processes some data in excel(two values per iteration), introduces that data into a range (two cells per iteration) and Copy/Pastes that range as an image (shape) into a given PowerPoint Slide at a given Top/Left value.

The macro works as intended but as the number of pasted shapes rises so do the chances of skipping random shapes (without error messages). I have tried the code several times and never have I experienced the same result: sometimes two or three shapes are missing, sometimes eight and sometimes none...

I am using DoEvents after each Paste and (just in case it helps) a Sleep function defined as: "Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)"

I there a way to combine all of the pasted images into one big image per slide from Excel? Does anyone have any other suggestions?

The following code has been taken from the macro and for simplicity's sake I ommited the rest. This code is placed inside two nested For-loops.

'Variable calculations
LastRow = .Cells(.rows.Count, .Range("statusCol").Column).End(xlUp).Row
LastCol = .Cells(61, .Columns.Count).End(xlToLeft).Column
Set rng = .Range(.Cells(61, .Range("statusCol").Column), .Cells(LastRow, LastCol))

rng.Copy

If ExcelApp.ClipboardFormats(1) Then    ' Check clipboard contents

    mySlide.Shapes.PasteSpecial DataType:=2  
    DoEvents

    ' Changing the pasted cell's position and size
    Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
    With myShape
        .LockAspectRatio = msoTrue
        .Left = TableLeft
        .Top = TableTop
        .Height = 20 * rng.rows.Count                  
    End With
End If

Upvotes: 0

Views: 911

Answers (1)

Mazt Entropy
Mazt Entropy

Reputation: 11

I have thought of a simple enough solution which has apparently done the trick:

oldCount = mySlide.Shapes.Count
' Pasting and verifying that it was done successfully
While (mySlide.Shapes.Count <= oldCount)
    mySlide.Shapes.PasteSpecial DataType:=2  
    DoEvents
Wend

Thus far just checking whether the number of shapes has increased has solved the problem.

Upvotes: 1

Related Questions