Shapes (Unknown Member) Error in Excel/Powerpoint VBA Macro [cannot copy cells]

[Kloakentier]

Dear Community,

I am trying to create a macro that creates a Powerpoint presentation which has a header and then shows the current month's balance sheet and P&L by copying it from Excel. For some reason it does not work. I have tried a lot of different approaches. And even after googling all the different error messages and going through all the proposed solutions, it still doesn't work for me. Here is the code.

Option Explicit
Sub ExportToPowerpoint()
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppSlide As PowerPoint.Slide
Dim WB As Workbook
Dim TestV AsString
Dim oshpRng As ShapeRange
Application.ScreenUpdating =False
Set WB = ThisWorkbook
Set ppApp =New PowerPoint.Application
ppApp.Visible =True
ppApp.Activate
Set ppPres = ppApp.Presentations.Open("C:\Users\[...]\Reporting_Template.pptx")
ppPres.Slides(1).Shapes(2).TextFrame.TextRange.Text = WB.Worksheets("Settings").Range("D5").Value &" 2018"
Set ppSlide = ppPres.Slides.Add(4, ppLayoutBlank)
ppSlide.Shapes.AddTextbox(msoTextOrientationHorizontal,30,13,800,0).TextFra me.TextRange.Text = _
WB.Worksheets("BS_LE").Range("A2").Value
ppSlide.Shapes(3).TextFrame.TextRange.Font.Name ="Verdana"
ppSlide.Shapes(3).TextFrame.TextRange.Font.Size =22
ppSlide.Shapes(3).TextFrame.TextRange.Font.Bold =False
ppSlide.Shapes(3).TextFrame.TextRange.Font.Color.RGB = RGB(80,50,145)
WB.Worksheets("BS_LE").Range("B4:F10").Copy
ppSlide.Shapes.Paste ' or also ppSlide.Shapes.PasteSpecial ppPastePNG
End Sub

I also tried adding any and all of the following approaches, which I found on StackOverflow and various other support forums:


ppApp.ActiveWindow.ViewType = ppViewNormal
DoEvents
ppSlide.Select
Set oshpRng = ppSlide.Shapes.PasteSpecial(ppPasteJPG)
ppPres.Slides(4).Shapes.PasteSpecial(ppPasteJPG)
ppApp.ActivePresentation.Slides(5).Shapes.PasteSpecial ppPastePNG
ppApp.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

None of them work. Here are the error messages: Either it says the clipboard is empty or the data type is unavailable. Sometimes it also says the Paste Action failed. Any idea, why this is happening and how to fix it? Any help would be greatly appreciated.
22334

[John Wilson]

Did you try


ppSlide.Shapes.PasteSpecial (ppPasteOLEObject)

[Kloakentier]

Hi John,

I tried all kinds of data types. The weird thing is, sometimes it works and it copy and pastes it fine. But then again it doesn't (majority of the time). I tried all of the solutions I can find, but even though things like DoEvents, Activating PowerPoint, Changing the view to ppViewNormal, minimizing PowerPoint, using GoToSLide etc, seem to solve the problem for others, NONE of it solves it for me. It either says, that the object type is not available or that the clipboard is empty or cannot be pasted here. I really have no idea what more to try... Do you?

[Kloakentier]

Hi John again,

so I tried the code on another machine and there it works fine. So it must be something with this machine. Do you have any hunch where this error might stem from?