Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have already found some helpful sites but I'm still unable to do what I want. My Excel file contains few columns and multiple rows. All the data from one row would be in one slide but data from different cells in that one row should go to a specific elements in PP slide. At first, is it possible to export data from an Excel cell into a specific text box in PP? For example, I would like to have all data from the first column of each row go to a Text box 1. Let's say I have 100 rows so I would have 100 slides and each slide would have Text bow 1 with correct data. Text box of slide 66 would have data from the first column of row 66. Then all data from the second column of each row would go to a text bow 2 and so on.

I tried to do some macros with bad success. I also tried to use Word outlines and export them into PP (New slide -> Slides from Outline) but there seems to be a bug since I got 250 pages of gibberish. I had only two paragraphs and both had one word. First paragraph used Heading 1 style and second paragraph used Normal style.

Sites what I have found, use VB and/or some other programming language to create slides from Excel sheets. I have tried to add those VB codes into my macros but none of them hasn't worked so far. Probably I just don't know how to use them correctly :) Here's some helpful sites:

VBA: Create PowerPoint Slide for Each Row in Excel Workbook

Creating a Presentation Report Based on Data

Question in Stackoverflow

I use Office 2011 on Mac. Any help would be appreciated!

share|improve this question
    
The first link will do most of the work for you, but instead of pasting the text from the first row into arbitrarily the first text box on the slide, you could step through each shape on the slide and look for, say, text like "Column 1", and replace that with the text from the cell in column 1 of the first row. –  Steve Rindsberg Aug 15 '11 at 18:44
    
I get an error when I try to run the macro. Error is on the line: "Set OWB = Excel.Application.Workbooks.Open("C:\list.xlsx")". I have changed to file path but PP keeps saying "ActiveX component cannot create object". What could be the problem? –  nqw1 Aug 16 '11 at 6:11
    
Have you added the reference to Excel's VBA library in VB Editor (via Tools > References)? Did you create an instance of Excel in your code? [ eg, Dim appXL as Excel.Application then Set appXL = CreateObject("Excel.Application") or Set appXL = GetObject(, "Excel.Application") ] –  variant Aug 16 '11 at 19:20
    
I'm following these instructions: craig-tolley.co.uk/2011/06/08/… and I had also added that reference. I don't know how to modify the code in instructions with the one you gave or is the code in instructions already enough –  nqw1 Aug 17 '11 at 6:28
add comment

2 Answers

Try something like this. I've made a few mods so that it replaces any instance of the text @COL1@ on the slide with values from the worksheet. Untested aircode, mind you.

Sub CreateSlides()
'Open the Excel workbook. Change the filename here.
Dim OWB As New Excel.Workbook
Set OWB = Excel.Application.Workbooks.Open("C:\list.xlsx")
'Grab the first Worksheet in the Workbook
Dim WS As Excel.Worksheet
Dim sCurrentText As String
Dim oSl As Slide
Dim oSh As Shape
Set WS = OWB.Worksheets(1)
Dim i As Long
'Loop through each used row in Column A
For i = 1 To WS.Range("A65536").End(xlUp).Row
    'Copy the first slide and paste at the end of the presentation
    ActivePresentation.Slides(1).Copy
    Set oSl = ActivePresentation.Slides.Paste(ActivePresentation.Slides.Count + 1)
    sCurrentText = WS.Cells(i, 1).Value

    ' find each shape with "@COL1@" in text, replace it with value from worksheet
    For Each oSh In oSl.Shapes
      ' Make sure the shape can hold text and if is, that it IS holding text
      If oSh.HasTextFrame Then
        If oSh.TextFrame.HasText Then
          ' it's got text, do the replace
          With oSh.TextFrame.TextRange
            .Replace "@COL1@", sCurrentText
          End With
        End If
      End If
    Next
Next
End Sub
share|improve this answer
    
Thanks for your help! I still get that same error :/ I get an error when I try to run the macro. Error is on the line: "Set OWB = Excel.Application.Workbooks.Open("C:\list.xlsx")". I have changed the file path but PP keeps saying "Error 429: ActiveX component cannot create object". I don't know what I'm doing wrong :/ –  nqw1 Aug 22 '11 at 5:49
    
I'm afraid that the answer to "What am I doing wrong" may be "You're using a Mac." As far as I know, there's no support for ActiveX objects under OSX/Office. There's a very smart guy, Ron DeBruin, an Excel expert, who's just fallen in love with his new Mac and is exploring Mac/PC VBA incompatibilities. He's posting his findings as he goes: rondebruin.nl/mac.htm If you don't find an answer there, you might want to look him up in the Mac Office section of answers.microsoft.com –  Steve Rindsberg Aug 22 '11 at 14:07
    
Thank you for your help and tips! I'll take a look. –  nqw1 Aug 26 '11 at 6:06
    
I extended the solution a bit - of course giving credit to this page where I took the solution for my problem from. locutus-one.blogspot.de/2012/08/… –  user153724 Aug 21 '12 at 21:10
add comment

The PowerPoint Add-In Excel Import can import Excel data to PowerPoint

What it does

  • imports Excel data to your open presentation
    Method: One Excel row per PowerPoint slide, one cell per text field on the current slide
  • can choose the Excel sheet to take data from if multiple sheets are present
  • can automatically add slides if your Excel file has more rows than slides in Powerpoint
  • warns you if there aren't enough text fields (shapes) to contain all data from a row
  • works with every Microsoft Office version

How to use

  • Download the Add-In
  • save it in your Micorsoft Office Add-In folder
    Tip: press Win+R and type %AppData%\Roaming\Microsoft\AddIns
  • Open PowerPoint and enable the Add-In
    • Excel 2003: Menubar → Tools → Add-ins
    • Excel 2007: Start-button → Excel Options → Add-Ins → drop down: Excel add-ins → Go
    • Excel 2010: File tab → Options → Add-Ins → drop down: Excel add-ins → Go
  • a new Menu Bar entry will pop up
    enter image description here
  • the new Import Excel data button will pop up a File dialog to choose your Excel file.
    If you have enough text fields, the Add-In will fill them

View this short imgur album to illustrate the Add-in


Note: If want to customize the code you have to add a registry key to see the Add-In code.
The source code can be viewed on pastebin

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.