Results 1 to 9 of 9

Question on References in VBA

This is a discussion on Question on References in VBA within the Excel Questions forums, part of the Question Forums category; I have a file that copies/pastes charts from Excel into PowerPoint, and to make this work, I had to check ...

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    535

    Default Question on References in VBA

    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

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Question on References in VBA

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,287

    Default Re: Question on References in VBA

    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

  4. #4
    Board Regular
    Join Date
    Sep 2010
    Posts
    535

    Default Re: Question on References in VBA

    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?

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Question on References in VBA

    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?

  6. #6
    Board Regular
    Join Date
    Sep 2010
    Posts
    535

    Default Re: Question on References in VBA

    Quote Originally Posted by RoryA View Post
    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.

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Question on References in VBA

    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

  8. #8
    Board Regular
    Join Date
    Sep 2010
    Posts
    535

    Default Re: Question on References in VBA

    Thanks. But the PowerPointApp.Presentations.Add line seems to bring PowerPoint to the front of your screen, regardless of the .Visible not being called.

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Question on References in VBA

    Interesting - it didn't for me in 2010 when I tested.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com