Learn how to a build a cloud-first strategyRegister Now


Excel Data to Powerpoint Label

Posted on 2011-10-27
Medium Priority
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
  • 2
LVL 17

Accepted Solution

andrewssd3 earned 2000 total points
ID: 37044391
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

ID: 37044405
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 31

Expert Comment

ID: 37044409
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

ID: 37051083
Excellent help!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

810 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