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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thanks. But the PowerPointApp.Presentations.Add line seems to bring PowerPoint to the front of your screen, regardless of the .Visible not being called.
 
Upvote 0
Interesting - it didn't for me in 2010 when I tested.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top