Macro with excel & Powerpoint

[Lazz04]

We use powerpoint games in our school, and the understanding/fixing the free
download using an excel spreadsheet for questions and a macro would make
using these types of games easier and more useful. Could you help me
troubleshoot why the code does not open a window so that I can choose the
spreadsheet to load?
PPT: Wheel of Jeopardy by Justin Kendrick
Website: http://www.pptmagic.com/downloads.htm

I am using PowerPoint2003 and windows XP.

Run time error 429 -Active x component cant create object

Which steps me to to
with cdgload the mouse over provides this message: object variable or with
variable not set.

Could you help me with how to fix this?

Helen


Sub LoadData()
'Create the common dialog box
Dim cdgLoad As New CommonDialog

'Set it up
With cdgLoad
.DialogTitle = "Load Data"
.Filter = "Excel Spreadsheets (*.xls)|*.xls|All Files (*.*)|*.*"
.ShowOpen
End With

'Extract the filename
Dim File As String
DataFile = cdgLoad.FileName

'Create the Excel sheet
Dim ExcelApp As Excel.Application
Set ExcelApp = CreateObject(Class:="Excel.Application")

'Set some options...
ExcelApp.Visible = False 'We don't want to see the thing
ExcelApp.DisplayAlerts = False 'Nor do we want to see message boxes

'Open the file...
ExcelApp.Workbooks.Open FileName:=DataFile, ReadOnly:=True,
AddToMru:=False

'Get the proper workbook
Dim DataSource As Excel.Workbook
For Each DataSource In ExcelApp.Workbooks
'FullName is actually the filename and path of the workbook
If DataSource.FullName = DataFile Then Exit For
Next

'Okay, now get some data!
Dim DataSheet As Excel.Worksheet 'A worksheet
Dim QuestionsSheet As Excel.Worksheet 'The worksheet with the questions
Dim CatRowSheet As Excel.Worksheet 'The worksheet with the
catagories and rows
Dim PhraseSheet As Excel.Worksheet 'The worksheet with the phrase

'Get the worksheets
For Each DataSheet In DataSource.Worksheets
If DataSheet.Name = "Questions" Then Set QuestionsSheet = DataSheet
If DataSheet.Name = "Catagories and Points" Then Set CatRowSheet =
DataSheet
If DataSheet.Name = "Phrase" Then Set PhraseSheet = DataSheet
Next

'Get the questions and answers
For Cat = 1 To 6 'category
For Row = 1 To 5
questionNum = ((Cat - 1) * 5) + Row 'The question number
when converted into a two-dimensional array
Globals.Question(Cat, Row).Question = QuestionsSheet.Range("D" &
Trim(Str(2 + questionNum))).Value 'Question
Globals.Question(Cat, Row).AnswerA = QuestionsSheet.Range("E" &
Trim(Str(2 + questionNum))).Value 'Answer A
Globals.Question(Cat, Row).AnswerB = QuestionsSheet.Range("F" &
Trim(Str(2 + questionNum))).Value 'Answer B
Globals.Question(Cat, Row).AnswerC = QuestionsSheet.Range("G" &
Trim(Str(2 + questionNum))).Value 'Answer C
Globals.Question(Cat, Row).AnswerD = QuestionsSheet.Range("H" &
Trim(Str(2 + questionNum))).Value 'Answer D
Select Case QuestionsSheet.Range("I" & Trim(Str(2 +
questionNum))).Value
Case "A"
Globals.Question(Cat, Row).Correct = 1
Case "B"
Globals.Question(Cat, Row).Correct = 2
Case "C"
Globals.Question(Cat, Row).Correct = 3
Case "D"
Globals.Question(Cat, Row).Correct = 4
End Select
Next
Next

'Get the category names and row values
For Cat = 1 To 6
Globals.Category(Cat) = CatRowSheet.Range("C" & Trim(Str(2 +
Cat))).Value
Next
For Row = 1 To 5
Globals.RowValue(Row) = Val(CatRowSheet.Range("F" & Trim(Str(2 +
Row))).Value)
Next

'Get the starting points and vowel cost
Globals.StartingPoints = Val(CatRowSheet.Range("D11"))
Globals.VowelCost = Val(CatRowSheet.Range("D12"))

'Get the phrase
For Col = 1 To 10
For Row = 1 To 4
Globals.Phrase(Col, Row) = UCase(PhraseSheet.Range(Chr(65 + Col)
& Trim(Str(Row + 1))))
Next
Next
Globals.PhraseCategory = PhraseSheet.Range("D7").Value

'Set startup defaults
ResetData

'We are done, so end the Excel instance.
DataSource.Saved = True 'Bypass the save dialog (Note that this
does not save, it just makes Excel think the document is saved)
ExcelApp.Quit
End Sub

 

[Bill Foley]

Helen,

I just got back to the NewsGroups (been off a week). Not sure about your
excel code, but have you tried using my Jeopardy game download? It is only
a single round sample, but it is easy to insert questions/answers. You can
download it at:

http://www.pttinc.com/ppgame.htm