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....

question answer tags PPT XLS.jpg

[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

Capture.JPG

[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

Capture.JPG

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

Capture.JPG

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

Capture.JPG

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.