Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting PowerPt to respond to VBE (xl2000)

    While I have considerable experience working solely within Excel and VBE, I've never tried to control/transfer-data to PowerPt.
    While PowerPt is on this system, it looks like Excel's VBE doesn't see it.
    My first attempt out of the box... (see: <post:=93,026>post 93,026</post:> )

    Sub Charts2Powerpoint()
    Dim ppApp As New powerpoint.Application
    ...etc.

    ...results in a compile error: User-defined type not defined.
    I think I could tell I was in trouble already when "powerpoint" would not capitalize itself in the Excel module.

    I also see that there are no Add-Ins listed within the PowerPoint application.

    What do I need? What am I (or this system) missing ? Aggghhh!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    In VBA you need to set a reference to the PPT object library:

    Tools - references
    Check "Microsoft Powerpoint 10.0 Object Library"
    <ok>

    Steve

  3. #3
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    Great! It works! Thanks, Steve.
    I'll probably be back with more questions as I explore this new (to me) area.

  4. #4
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    Another question comes to mind:

    Using VBE, how to assure that this Reference is indeed installed/enabled?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    If you set a reference, it is stored with the workbook. So if the workbook is opened on another PC, the reference will still be set. It'll work if the user has PowerPoint 2000 or a more recent version installed. It won't work if the user has PowerPoint 97 or doesn't have PowerPoint at all.

  6. #6
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    Thanks, Hans

  7. #7
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    Now that you've helped me reach success in copying Excel charts into Powerpoint, I'm confronted with two additional problems:

    1) I've also been checking my Excel job out on Excel97 since there is still a little chance someone may wish to use it there. I found that, once this transfer operation to Powerpoint was added, everything works fine when I manually select the reference "Microsoft Powerpoint 8.0 Object Library" to replace the (now) missing "Microsoft Powerpoint 10.0 Object Library". The question then is: Is there a way for VBE-code to identify the current Excel-version and engage the appropriate Powerpoint object library (de-selecting the alternate 'missing' library reference in the process)?

    2) Once the chart transfer to Powerpoint is complete, I'd like to have the process change the PP-slides' background color (preferably to match the chart bacground). Again, I'm admitting to my considerable ignorance of how to control one app from another. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45> (Suggest any good tutorials about MSOffice inter-app programming?)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    1) Try Setting the reference in the file to the oldest version that will need it. Sett the reference in XL97 and save the file. I think the later versions will chose the later version OK. The problem is the earlier version not being compatible with the later version.

    2) I think this is a question more geared towards PPT since it uses the VB object model from powerpoint and not excel's VB. A simple way to discover the "syntax" is to create a macro in powerpoint with the recorder active, this will give you the appropriate objects. The only difference is that you must (if you are in excel) explicitly reference the powerpoint application object you created.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    1) To manipulate references in code, you must set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library, but ... this library doesn't work in Office 97, only in Office 2000 and later, so it won't help you.
    You could create the distribution version of your workbook in Excel 97, with a reference to the PowerPoint 8.0 library. If the workbook is opened in a later version, the reference should update itself automatically.
    Or create separate versions for Excel 97 and for Excel 2000.

    2) When I want to know how to code something in another application, I often start by recording a macro in that application and looking at the generated code. After tweaking the code (if necessary), you can use it in your automation code in Excel provided you prefix the objects with the appropriate PowerPoint object, in many cases the PowerPoint.Application object. Here is a (slightly modified) example of a macro recorded in PowerPoint:

    Sub Macro1()
    With ActiveWindow.Selection.SlideRange
    .FollowMasterBackground = msoFalse
    .DisplayMasterShapes = msoTrue
    With .Background
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = vbBlue
    .Fill.Transparency = 0#
    .Fill.Solid
    End With
    End With
    End Sub

    To use this code in Excel, you only need to change

    With ActiveWindow.Selection.SlideRange

    to

    With ppApp.ActiveWindow.Selection.SlideRange

    where ppApp is your PowerPoint.Application object.

  10. #10
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting PowerPt to respond to VBE (xl2000)

    Thanks again, Hans. For #2, it now works like a charm! I'll check out #1 ideas later. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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