PDA

View Full Version : Link form controls in PowerPoint to Excel?



garywood84
06-09-2007, 05:41 PM
I have some PowerPoint slides which have form controls on them (i.e. text boxes and radio buttons). Is it possible to link these to an Excel sheet and copy the values to it?

Here's what I'm trying to do. I'm doing a survey which will present respondents with pictures and they'll be asked to choose one. Say they choose picture A, then I want an A to appear in cell A1 of an Excel sheet. Then, for the next picture, they might choose B, so B should appear in cell A2...and so on through all the slides.

To complicate things just a little bit more, the next participant's responses should go in the same Excel sheet, just starting from the next blank row. That is, if there are ten slides, participant 2's responses will be in rows 11-20.

I know this is very complicated and am not sure it's possible, but if anyone can help, I'll be very grateful.

Thanks,

Gary

kunguito
06-17-2007, 04:35 PM
Hi garywood84,


First of all to transfer information to excel there are several ways:
support.microsoft.com/kb/247412/en-us

I think the high level approach would be to have a global array that you update each time you click on a command button of a slide. Afterwards it should open an excel worksheet and copy the conents of the variable on the first empty row.

Click twice on a command button to write the code for the Slide, you will be writing it in a Slide module. I am not sure if you can reuse the code for a slide to all of them.


This code (once it's completed, I'm now tired) should be created in another module.


Sub Test_A_or_B()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim firstEmpty As Long
'Run the presentation to generate an Array (Vector) of As and Bs (in a global variable, NOT COMPLETE, in fact you may not even need it)
With ActivePresentation.SlideShowSettings
.RangeType =
.AdvanceMode = ppSlideShowUseSlideTimings
.Run
End With
'Start a workbook document in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("c:\whatever you want to open.xls")
Set oSheet = oBook.Worksheets(1)
'Find the first empty row in the Worksheet
firstEmpty = Worksheets(1).Cells.Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
'Copy the array into that row(NOT COMPLETE)
oSheet.Range("A1:B1").Value =
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\however you want to save it.xls"
oExcel.Quit
End Sub