Create power point using excel macro
I managed to figure it out coding it myself in the excel macro. It's not the best solution, but it is easy to follow and can be modified by someone with this same issue. FYI I am the asker of this question, but my computer is in serious need of reimage and I can't log in to stack overflow...O well. Here is my code that solved this problem. Please note that all of the question were sorted by category prior so I simply changed the start and end loop control variables to make new ppts after saving and closing the previously created ones. The following code may contain code borrowed from other stack overflow questions and is repurposed:
Sub CreatePowerPointQuestions()
'Add a reference to the Microsoft PowerPoint Library by:
'1. Go to Tools in the VBA menu
'2. Click on Reference
'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay
'First we declare the variables we will be using
Dim newPowerPoint As PowerPoint.Application
Dim activeSlide As PowerPoint.Slide
Dim Question As String
Dim Options As String 'comma separated list of options
Dim Choices() As String 'split up options for printing
Dim printOptions As String 'string to print in contents of slide
Dim Answer As String
Dim limit As Integer
'set question amount:
limit = 5
'Look for existing instance
On Error Resume Next
Set newPowerPoint = GetObject(, "PowerPoint.Application")
On Error GoTo 0
'Let's create a new PowerPoint
If newPowerPoint Is Nothing Then
Set newPowerPoint = New PowerPoint.Application
End If
'Make a presentation in PowerPoint
If newPowerPoint.Presentations.Count = 0 Then
newPowerPoint.Presentations.Add
End If
'Show the PowerPoint
newPowerPoint.Visible = True
'Select worksheet and cells activate
Worksheets("Sheet1").Activate
'Loop through each question
For i = 1 To limit
'Add a new slide where we will paste the Question and Options:
newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)
'Set the variables to the cells
Question = ActiveSheet.Cells(i, 1).Value
Options = ActiveSheet.Cells(i, 2).Value
Answer = ActiveSheet.Cells(i, 3).Value
'Split options into choices a,b,c,d based on comma separation
Choices() = Split(Options, ", ")
'Formate printOptions to paste into content
printOptions = "A) " & Choices(0) & vbNewLine & "B) " & Choices(1) & vbNewLine & "C) " & Choices(2) & vbNewLine & "D) " & Choices(3)
activeSlide.Shapes(2).TextFrame.TextRange.Text = printOptions
'Set the title of the slide the same as the question for the options
activeSlide.Shapes(1).TextFrame.TextRange.Text = Question
'Add answer slide and select it
newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)
'Set title:
activeSlide.Shapes(1).TextFrame.TextRange.Text = "Answer:"
'Set contents to answer:
activeSlide.Shapes(2).TextFrame.TextRange.Text = Answer
'Finished with a row (question)
Next
AppActivate ("Microsoft PowerPoint")
Set activeSlide = Nothing
Set newPowerPoint = Nothing
End Sub
Comments
-
Clint L almost 2 years
I have an interesting problem I am unsure of. I have not worked with power point and have little excel macro experience. I have found many similar issues to mine but none of them quite fit the bill. I am helping my local charity with a fund raiser and need a way to make a triva sort of game. The game will be displayed with powerpoint, and all the questions, choices, and answers are in an excel sheet. The way it is laid our is one question per row, and the columns are: Question, options, answers and category.
I have managed the category sorting quite easy enough, but now I need to somehow work with creating power point slides in such a way so that the question is the title, with the options being the content, and then the following slide is the answer to that question. Therefore each question creates two slides, a question and answer slide.
Example row (| denote column):
Which of these was an italian sculptor? | Michelangelo, tintoretto, da vinci, galilleo | michelangelo | Art
So the result would be a side with title "Which of these was an italian sculptor?" and content a) Michelangelo, b) tintoretto, c) da vinci, d) galilleo
the following slide simply being "Michelangelo"