Question on References in VBA

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I have a file that copies/pastes charts from Excel into PowerPoint, and to make this work, I had to check the box next to the Microsoft PowerPoint 15.0 Object Library. Related to this:

1. If I send the file to someone else, will they need to manually tick that box, or does the selection follow the file?

2. Is there a way to tick the box programmatically, such as when the file is opened? And if this is needed... how to accommodate for the fact that some people might have different versions (i.e. maybe not all 15.0)?

Thanks
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
The reference is saved with the file but you will get issues if the file is being passed between people with different versions of Office and they are all saving it. It would be better to remove the reference and late bind the code (I have a short article about this here). If you need help with that, please post the code.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
1. The user will need to add the relevant reference.

2. Probably but there is a simpler solution.

Use Late-Binding rather than Early Binding in your code, this means you will not need to add a reference but you will lose intelli-sense when writing any additional code.

https://support.microsoft.com/kb/245115?wa=wsignin1.0
 

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
Thanks... one other generic question... my macro sets ScreenUpdating to False, but when I use lines like this,

newPowerPoint.Visible = True

PowerPoint still comes to the front of the screen and I see all of the copying/pasting going on. Is there any way to turn off screen updating throughout all applications?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Screenupdating is application specific (it is, after all, a property of the Application object). Why do you make the PowerPoint app visible before it's ready?
 

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
Screenupdating is application specific (it is, after all, a property of the Application object). Why do you make the PowerPoint app visible before it's ready?
The site where I got the code from,

Copy & Paste An Excel Range Into PowerPoint With VBA

Says PowerPoint must be visible for the macro to work.

However, I commented the line and it still works, but it also still brings PowerPoint to the front. .Visible seems to make no difference. So I'm back to wanting to not show the PowerPoint work being done until the code has completed.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Try this - it's a late bound version of the code you linked to which doesn't show PP until the end:

Code:
Sub ExcelRangeToPowerPoint()

    Dim rng                   As Excel.Range
    Dim PowerPointApp         As Object
    Dim myPresentation        As Object
    Dim mySlide               As Object
    Dim myShapeRange          As Object

    Const ppLayoutTitleOnly   As Long = 11
    Const ppPasteEnhancedMetafile As Long = 2

    'Copy Range from Excel
    Set rng = ThisWorkbook.ActiveSheet.Range("A1:D12")

    'Create an Instance of PowerPoint
    On Error Resume Next

    'Is PowerPoint already opened?
    Set PowerPointApp = GetObject(class:="PowerPoint.Application")

    'Clear the error between errors
    Err.Clear

    'If PowerPoint is not already open then open PowerPoint
    If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

    'Handle if the PowerPoint Application is not found
    If Err.Number = 429 Then
        MsgBox "PowerPoint could not be found, aborting."
        Exit Sub
    End If

    On Error GoTo 0


    'Create a New Presentation
    Set myPresentation = PowerPointApp.Presentations.Add

    'Add a slide to the Presentation
    Set mySlide = myPresentation.Slides.Add(1, ppLayoutTitleOnly)

    'Copy Excel Range
    rng.CopyPicture xlScreen, xlPicture

    'Paste to PowerPoint and position
    mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
    Set myShapeRange = mySlide.Shapes(mySlide.Shapes.Count)

    'Set position:
    myShapeRange.Left = 234
    myShapeRange.Top = 186

    'Clear The Clipboard
    Application.CutCopyMode = False
    PowerPointApp.Visible = True

End Sub
 

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
Thanks. But the PowerPointApp.Presentations.Add line seems to bring PowerPoint to the front of your screen, regardless of the .Visible not being called.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Interesting - it didn't for me in 2010 when I tested.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,285
Messages
5,485,848
Members
407,521
Latest member
marita

This Week's Hot Topics

Top