How to embed Q&As into PPT for custom output later...?

[TrentLane]

Since John has better solutions above my level of PPT knowledge, I'll ask before I jump and create something :-)


Use case: I'm using PPT to create SCORM instructional modules. I have the questions and answers in a separate spreadsheet, cross-referenced for human use by mentioning the Slide ID. Using John's previous suggestion, I'm now using tags to define applicability of slides to a specific audience. I have it creating derivate PPTs based on the type of audience I select (which compares it against tags per slide defining whom (what audience) that slide applies to). This does nothing for the quiz questions though.


Is there a way to embed questions and answers into PPT so that I can create audience specific XLS files in the same way a derivative PPT is created? e.g. In the GUI I made, if I select Audience Type = Group A, then it creates a PPT based on any slide tagged as Group A and likewise creates a question/answer XLS by scanning for questions/answers tagged as Group A.


This illustration explains it better....
27977

[Paul_Hossler]

If I were doing it, I'd create Custom Slide shows from my 'master' presentation and try to avoid using Excel at all, except maybe to build the master

27982

[TrentLane]

If I were doing it, I'd create Custom Slide shows from my 'master' presentation and try to avoid using Excel at all, except maybe to build the master

27982

Thanks for the suggestion. I failed to explain that the Q&A's are not ultimately to be presented and compiled with PPT. Excel spreadsheets are the primary input format to another program that converts them into a SCORM HTML5 container that reports grade results to a SCORM API compliant Learning Management System. The disconnect is that the lessons are generated into HTML 5 SCORM modules from PPT but they share no programmatic tie to the questions/answers.

In other words, for each variant of the PPT, I would have to manually create an XLS that includes only those questions that apply.

So if I create a PPT for Group B, M, and Z, I'd have to audit every question to make sure they don't apply to other than Group B,M, and Z.

I had thought that maybe I can generate the questions alongside the PPT variants by exploiting the same slide-by-slide tag meta-data...

[Paul_Hossler]

You could use Custom Slide Shows and the Note page to select just the Q&A for each group, extract the Note and pass to Excel

27989

I just added to a string and Msgbox



Option Explicit


Sub Example()
Dim oPres As Presentation
Dim oSettings As SlideShowSettings
Dim oNamed As NamedSlideShow
Dim iSlideID As Long
Dim oSlide As Slide
Dim oShape As Shape
Dim sForExcel As String


Set oPres = ActivePresentation
Set oSettings = oPres.SlideShowSettings
Set oNamed = oSettings.NamedSlideShows("Show For 134") ' <<<<<<<<<<<<<name of custom slide show


With oNamed
For iSlideID = LBound(.SlideIDs) To UBound(.SlideIDs)
If .SlideIDs(iSlideID) > 0 Then

Set oSlide = oPres.Slides.FindBySlideID(.SlideIDs(iSlideID))

sForExcel = "SlideID = " & oSlide.SlideID & " -- " & vbCrLf & vbCrLf

If oSlide.Shapes.HasTitle Then sForExcel = sForExcel & oSlide.Shapes.Title.TextFrame.TextRange.Text & vbCrLf & vbCrLf

If oSlide.HasNotesPage Then
For Each oShape In oSlide.NotesPage.Shapes
If oShape.PlaceholderFormat.Type = ppPlaceholderBody Then
If oShape.HasTextFrame Then
If oShape.TextFrame.HasText Then
sForExcel = sForExcel & oShape.TextFrame.TextRange.Text & " -- "
End If
End If
End If
Next
End If

MsgBox sForExcel ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Give to Excel

End If
Next
End With
End Sub

[TrentLane]

You could use Custom Slide Shows and the Note page to select just the Q&A for each group, extract the Note and pass to Excel

27989

I just added to a string and Msgbox



Option Explicit


Sub Example()
Dim oPres As Presentation
Dim oSettings As SlideShowSettings
Dim oNamed As NamedSlideShow
Dim iSlideID As Long
Dim oSlide As Slide
Dim oShape As Shape
Dim sForExcel As String


Set oPres = ActivePresentation
Set oSettings = oPres.SlideShowSettings
Set oNamed = oSettings.NamedSlideShows("Show For 134") ' <<<<<<<<<<<<<name of custom slide show


With oNamed
For iSlideID = LBound(.SlideIDs) To UBound(.SlideIDs)
If .SlideIDs(iSlideID) > 0 Then

Set oSlide = oPres.Slides.FindBySlideID(.SlideIDs(iSlideID))

sForExcel = "SlideID = " & oSlide.SlideID & " -- " & vbCrLf & vbCrLf

If oSlide.Shapes.HasTitle Then sForExcel = sForExcel & oSlide.Shapes.Title.TextFrame.TextRange.Text & vbCrLf & vbCrLf

If oSlide.HasNotesPage Then
For Each oShape In oSlide.NotesPage.Shapes
If oShape.PlaceholderFormat.Type = ppPlaceholderBody Then
If oShape.HasTextFrame Then
If oShape.TextFrame.HasText Then
sForExcel = sForExcel & oShape.TextFrame.TextRange.Text & " -- "
End If
End If
End If
Next
End If

MsgBox sForExcel ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Give to Excel

End If
Next
End With
End Sub


Fantastic recommendation. This gives me an idea.... since you're using a shape, I could use your code and put the Q&As into shapes rather than the notes section. The shapes could be off slide in the margins. I just need to think about how to handle the various question types (multiple choice, multi-select, T/F, fill in the blank) then export that to excel in the format the other program needs. This will use what I already developed using Tags embedded into each slide combined with your code. I can't easily use the notes section because it's already being used for lots of other data.. unless I do some serious string processing to extract out a questions/answers section from it... perhaps some kind of XML-like structure would be best to have so many different types of data inside the notes section.

The bad news is... I now have to get back to writing a lesson urgently so I can't get to this for a few weeks :-(

[Paul_Hossler]

Personally, I'd think about leaving the Q&As in Notes. I've found that Notes are easier are to work with than shapes. Other hand, shapes can have a Name

You could easily come up with your own structure/convention for the different types of questions and let Excel decode and format

e.g.


Q-TF-Do Cows Eat Grass
A-T

Q-MC-What do cows eat?
A1-Grass
A2-Meat
A3-Apples
A4-Alligators
A-A1

etc.