views:

149

answers:

1

I have about 25 PowerPoint presentations, each with at least 45 slides. On each slide is a question with four possible answers and a help button which provides a hint relevant to the question. Each of the answers and the help button is a PowerPoint Action button that launches a macro.

I am attempting to migrate all the questions/answers/hints into a SQL Database. I've worked with Office.Interop before when working with Excel and Word and I have plenty of SQL DB experience, so I don't foresee any issues with actually extracting the text portion of the question and answer and putting it into the db.

What I have no idea how to do is given an object on a slide -> get the action button info -> Get the macro name -> and finally get the macro's vb code. From there I can figure out how to parse out which is the correct answer and what the text of the hint is.

Any help/ideas would be greatly appreciated.

+2  A: 

To get the name of your items Run Macro settings, you'll run something like this:

Sub ActionSettingName()
    Dim p As Presentation
    Set p = ActivePresentation
    Dim s As Slide
    Dim sh As Shape
    Dim macroName As String
    For Each s In p.Slides
        For Each sh In s.Shapes
            If sh.Type = msoGroup Then
                Dim gs As Shape
                For Each gs In sh.GroupItems
                    PrintMacroName gs
                Next
            Else
                PrintMacroName sh
            End If
        Next
    Next
End Sub
Sub PrintMacroName(sh As Shape)
    If sh.ActionSettings(ppMouseClick).Action = ppActionRunMacro Then
        macroName = sh.ActionSettings(ppMouseClick).Run
        Debug.Print macroName
    End If
End Sub

UPDATE: To get ActionSettings for TextRanges, please find below:

Sub ActionSettingName()
    Dim p As Presentation
    Set p = ActivePresentation
    Dim s As Slide
    Dim sh As Shape
    For Each s In p.Slides
        For Each sh In s.Shapes
            Dim tr As TextRange
            Set tr = sh.TextFrame.TextRange
            Dim macroName As String
            For i = 1 To tr.Runs.Count
                macroName = tr.Runs(i).ActionSettings(ppMouseClick).Run
                If Len(macroName) > 0 Then
                    Dim runText As String
                    runText = tr.Runs(i).Text
                    Debug.Print "RUN: " & runText & vbCrLf & "MACRO: " & macroName
                End If
            Next
        Next
    Next
End Sub

Then you'll want to search for and extract that macro (and anything else you need) from the VBE inside the last For/Next loop.

There are a few references on SO on how to extract all macros in Excel/Word and work with the VBProject model - the techniques are identical to PowerPoint. See Programmatically extract macro (VBA) code from Word 2007 docs for one of the better examples. A much more comprehensive intro to the VBProject object model exists at Programming The VBA Editor.

Otaku
So I guess for a "Normal" situation this would work great... I was able to use the above method to extract the macro name for the hint since it's its own separate action button. For some reason the question and answers are all in the same shape (text block)... and all the answers are also action buttons. I'm not really sure how that works having 4 action buttons that are all mouse click in the same shape. I tried querying all shapes on the slide just to make sure they weren't hiding somewhere else but I couldn't find them. Any ideas?
Patricker
@Patricker: Here's what I'm thinking - those Action Buttons are grouped with a textbox. I've updated the code above that will help get the Run Macros for grouped shapes.
Otaku
@Otaku ... doesn't look like that's it either. Here are the steps to reproduce my exact issue: 1) create a new presentation. In a text box type three lines of text. 2) Add a macro to your presentation. 3) Hilight one of the lines of text inside the text box, go to the insert tab on the ribbon, and choose Action. 4) Choose the macro option and then choose the macro from the list. 5) repeat this step for the third line (leave the second line as normal text). Now get the vba to see the action...
Patricker
@Patricker: Looks like the creator wanted to offer users the choice of clicking on the Action Button or the text beside it. Nice touch from a usability perspective. I've updated the code above to get these ActionSettings - the secret sauces here requires the use of `Runs` - it doesn't work with `Characters`, `Lines`, `Sentences`, or `Paragraphs`.I'll leave the other code up as well in case it helps anyone else in the future.
Otaku
@Patricker: Did this get you what you need?
Otaku
@Otaku: Sorry for the delay, been out sick. Awesome code, thanks a ton!
Patricker