Excel Data to Powerpoint Label

Posted on 2011-10-27
Last Modified: 2012-06-27

I have an excel file which acts as a benefit calculator. Each time we use it for a customer, we need to manually copy the results of (30 fields) of data into a powerpoint sales pack template that is then printed to PDF and sent to the customer.

Due to the volume of transactions, my boss has tasked me with investigating the possibility of automating the entire process.

I have enclosed two sample files to help with this question:

1. Input file - This is the excel file and I have placed values in two locations. The values are what is needed to be exported to powerpoint. I have also created a command button to execute the upload process.
2. Output file - this is the powerpoint file with two labels named label1 and label2.

Is it possible for someone to help me achieve this from start to finish?

Question by:creativefusion
    LVL 17

    Accepted Solution

    This simple code will do what you ask in the question - I guess you will be able to adapt it to do whatever other transfers you need.  This just gets the ppt presentation, and makes the changes - it does not currently do any saving.  Hopefully it's clear what's going on.  I'm sure you're aware your Labels are ActiveX objects - it had me confused for a while thinking they were ppt shapes.  If they were you could use the shape.textframe.textrange.text property to change the text.
    Private Sub Cmd1_Click()
        Dim pptApp As PowerPoint.Application
        Dim pptPres As PowerPoint.Presentation
        Dim shp As PowerPoint.Shape
        Dim objLabel As MSForms.Label
        Set pptPres = GetObject("your file path...\output.pptx")
        Set pptApp = pptPres.Application
        pptApp.Visible = msoCTrue
        ' get the first shape - it is an ActiveX Label object, so need to get that from the OLEFormat
        Set shp = pptPres.Slides(1).Shapes("Label1")
        Set objLabel = shp.OLEFormat.Object
        objLabel.Caption = ActiveWorkbook.Worksheets("Sheet1").Range("A3").Value
        Set shp = pptPres.Slides(1).Shapes("Label2")
        Set objLabel = shp.OLEFormat.Object
        objLabel.Caption = ActiveWorkbook.Worksheets("Sheet1").Range("B3").Value
    End Sub

    Open in new window

    LVL 17

    Expert Comment

    Sorry - that also assumes you have added a reference to Powerpoint in the VBA project.  If you don't want to do this you can switch to late binding by changing the Powerpoint.Application, Powerpoint.Presentation and Powerpoint.Shape definitions to 'Object'
    LVL 29

    Expert Comment

    1) is your output.pptx. file alread created and you need to add to it the info that is in the excel Input.xlsx or it is a new file everytime that will be created ?

    2) In your sample Input.xlsx there is

            A                 B
    2   Cost Year  Cost Week
            200      150.25

    However in the output.pptx
    there is only
    Label1 and Label2

    Don't see what you imported from excel to ppt unless you attached the wrong file !???


    Author Closing Comment

    Excellent help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now