Consulting

Results 1 to 4 of 4

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

  1. #1

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

    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).TextFrame.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 ppPastePNGEnd 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.
    error vba.jpg
    Last edited by Kloakentier; 05-29-2018 at 06:09 PM. Reason: Formatting

  2. #2
    VBAX Master
    Joined
    Feb 2007
    Posts
    1,686
    Location
    Did you try

    ppSlide.Shapes.PasteSpecial (ppPasteOLEObject)
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

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

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

Tags for this Thread

Posting Permissions

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