VBA saving data from ppt to excel possible?

[bungle000]

Hi there, please forgive me as I am a complete novice and trying my darned hardest to learn. I created a PowerPoint quiz using various snippets found with the help of the wonderful google. It has VBA behind it to store a users name and score and bring it all up in a final printable slide (see code below) what i now want to do, if it is at all possible, is to automatically store the data received within & firstname &, & lastname &, & numcorrect & into an excel database within a preset column on the next available row for example first name would go into the next available row on column A etc.

Like i said i dont know if this is at all possible and at the moment it seems my lucky streak with finding and compiling the information has run out.

below is the code for the printable slide, it may not be too tidy or professional (apologies ) but at the moment it works just fine


Sub PrintablePage()
Dim printableSlide As Slide
Dim homeButton As Shape
Dim printButton As Shape
Set printableSlide = _
ActivePresentation.Slides.Add(Index:=printableSlideNum, _
Layout:=ppLayoutText)
printableSlide.Shapes(1).TextFrame.TextRange.Text = _
"PPT Title "
printableSlide.Shapes(1).TextFrame.TextRange.Font.Size = 20
printableSlide.Shapes(2).TextFrame.TextRange.Text = _
"Results for " & firstname & " " & lastname & Chr$(13) & _
"PPT Title" & Chr$(13) & _
"You got " & numCorrect & " out of " & _
numCorrect + numIncorrect & "." & Chr$(13) & _
"If you got at least 5 correct, press the Print Results button to print your answers." & Chr$(13) & _
"If you did not get at least 5 correct, press the Start Again button."
printableSlide.Shapes(2).TextFrame.TextRange.Font.Size = 18
Set homeButton = _
ActivePresentation.Slides(printableSlideNum).Shapes.AddShape _
(msoShapeActionButtonCustom, 140, 380, 130, 30)
homeButton.TextFrame.TextRange.Text = "Start Again"
homeButton.ActionSettings(ppMouseClick).Action = ppActionRunMacro
homeButton.ActionSettings(ppMouseClick).Run = "StartAgain"
Set printButton = _
ActivePresentation.Slides(printableSlideNum).Shapes.AddShape _
(msoShapeActionButtonCustom, 420, 380, 130, 30)
printButton.TextFrame.TextRange.Text = "Print Results"
printButton.ActionSettings(ppMouseClick).Action = ppActionRunMacro
printButton.ActionSettings(ppMouseClick).Run = "PrintResults"
ActivePresentation.SlideShowWindow.View.Next
ActivePresentation.Saved = True
End Sub

[John Wilson]

There are several ways the easiest is probably to write to a CSV file which will open in Excel


Sub WriteFile(firstname As String, lastname As String, numcorrect As String, numincorrect As String)
' this writes the results to as csv file on the desktop
Dim strFilename As String
Dim intNum As Integer
strFilename = Environ("USERPROFILE") & "\Desktop\results.csv"
intNum = FreeFile
Open strFilename For Append As intNum
Print #intNum, firstname & "," & lastname & "," & numcorrect & "," & numincorrect
Close intNum
End Sub


Call this from you existing code by adding at the end


Call WriteFile(firstname, lastname, numcorrect, numincorrect)

[bungle000]

Thank you John, I will try it as soon as I can and let you know the outcome. Very much appreciated

[bungle000]

This did not work john but thank you for your response and trying to help

[John Wilson]

Maybe post your code because it does work! You may need to amend to get exactly what you need of course but run this


Sub chexcode()
Call WriteFile("John", "Wilson", "10", "13")
End Sub




Sub WriteFile(firstname As String, lastname As String, numcorrect As String, numincorrect As String)
' this writes the results to as csv file on the desktop
Dim strFilename As String
Dim intNum As Integer
strFilename = Environ("USERPROFILE") & "\Desktop\results.csv"
intNum = FreeFile
Open strFilename For Append As intNum
Print #intNum, firstname & "," & lastname & "," & numcorrect & "," & numincorrect
Close intNum
End Sub

[John Wilson]

You should see a file on the Desktop

[bungle000]

I got it to create a file by adding Dim to firstname As String etc however it does not fill in the data

[bungle000]

Sorry john I am responding on my phone as I am about to drive home so I didn't see your request for the code at first. I will post the code as soon as possible be prepared it is quite big as its a template with multi function.

[bungle000]

John I managed to change the code very slightly to also get it to write the details to the file. At the moment it is only writing all details in column A but this has given me a huge head start and I am sure I will figure it out.
Thank you your help is very much appreciated

[John Wilson]

Make sure you include the commas in the string to write. It's the comma that starts a new column.

[bungle000]

I feel like such an idiot because I knew that but just had a brain fart. Thanks again john