Announcement

Collapse
No announcement yet.

Merge data to PowerPoint certificates without Word

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Merge data to PowerPoint certificates without Word

    Hi all --

    I have an Excel file that contains student data, and I also have a PowerPoint file containing 25 certificate templates. I use the Excel workbook to track student data throughout my course, and at the end of training, I print out certificates in PPT for those who successfully pass. As it stands now, I am copying/pasting names into each certificate manually, and I'd like to setup some sort of merge to take care of this for me.

    My understanding is that it's not possible to Mail Merge from XL to PPT, and from reading through the other 'mail merge' threads on this forum, I'm assuming I'd have to:
    Mail Merge from Excel to Word, then from Word to PPT.

    Is that the case, or is there some way to do this without involving Word? Ideally, I'd like to feature a button in my Excel file which would start the merge, but before I start working on something like that, I just need to find out what my options are.

    TIA!

  • #2
    Re: Merge Xcel data to PowerPoint certificates - any way to skip Word?

    You can write vba code that would produce a ppt(s) file for each student.
    The name, grade, score etc can be read from excel and populated into know areas of the slidem such as textboxes.

    Can you post an example excel file and ppt template, you will have to zip the files in order to post a ppt.

    Cheers
    Andy

    Comment


    • #3
      Re: Merge Xcel data to PowerPoint certificates - any way to skip Word?

      sweather
      Junior Member ##

      You have clearly avoided the use of the word Excel in the subject title - can this please be edited? This is not on and mess up the forums search agility's for every member

      Please read THIS PAGE IN FULL.
      jiuk

      Comment


      • #4
        Re: Merge Xcel data to PowerPoint certificates - any way to skip Word?

        PM received ALL ok

        jiuk

        Comment


        • #5
          Re: Merge Xcel data to PowerPoint certificates - any way to skip Word?

          @ JIUK --> Thanks for editing the title for me!

          I'm attaching a zip file with the basics of what I'm looking to do. There is an excel file with two sheets and a powerpoint file with the certificate template.

          Thanks for any help!
          Attached Files

          Comment


          • #6
            Re: Merge data to PowerPoint certificates without Word

            This is the merge macro in excel
            Sub CreateCert()

            Dim shtStudent As Worksheet
            Dim shtTrainer As Worksheet
            Dim strStudent As String
            Dim strTrainer As String
            Dim lngRow As Long
            Dim objPPT As Object
            Dim objPres As Object
            Dim objSld As Object
            Dim objShp As Object

            Set shtStudent = Worksheets("StudentSheet")
            Set shtTrainer = Worksheets("TrainerSheet")

            Set objPPT = CreateObject("Powerpoint.Application")
            objPPT.Visible = True
            Set objPres = objPPT.presentations.Open(ThisWorkbook.Path & "\testpowerpoint.ppt")
            objPres.SaveAs ThisWorkbook.Path & "\certs.ppt"

            lngRow = 4
            Do While shtStudent.Cells(lngRow, 2) <> ""

            strStudent = shtStudent.Cells(lngRow, 3) & " " & shtStudent.Cells(lngRow, 2)
            strTrainer = shtTrainer.Cells(lngRow, 2)

            Set objSld = objPres.slides(1).Duplicate
            For Each objShp In objSld.Shapes
            If objShp.HasTextFrame Then
            If objShp.TextFrame.hastext Then
            objShp.TextFrame.TextRange.Replace "<Student_Name>", strStudent
            objShp.TextFrame.TextRange.Replace "<trainer>", strTrainer
            End If
            End If
            Next
            lngRow = lngRow + 1
            Loop
            objPres.slides(1).Delete
            objPres.Save
            objPres.Close

            End Sub


            In the template file I modified the 2 textboxes for trainer and student to contain a fixed text item that I could use as a placeholder.
            The date textbox uses the built in option to display a date, which should update its self with todays date. If you want control over the date just add a place holding bit of text and a additional replace command.

            I have attached both revised files.
            Attached Files

            Cheers
            Andy

            Comment


            • #7
              Re: Merge data to PowerPoint certificates without Word

              AWESOME ANDY!!!
              Thanks very much for this! Wow, it looks a bit more complicated than I expected!

              I'll play around with it and let you know how it works out!

              Thanks again!!!!!!!!!! x 1,000,000

              Comment


              • #8
                Re: Merge data to PowerPoint certificates without Word

                @Andy -- thanks again! Your solution works 100%!! :D

                Comment


                • #9
                  Re: Merge data to PowerPoint certificates without Word

                  Thanks for letting us know

                  Cheers
                  Andy

                  Comment


                  • #10
                    Re: Merge data to PowerPoint certificates without Word

                    ANDY ! This just saved me TONS of work! Thanks!!!

                    Comment


                    • #11
                      Re: Merge data to PowerPoint certificates without Word

                      Originally posted by Andy Pope View Post
                      Thanks for letting us know
                      Thanks so much - this worked beautifully for me as well! 70 certificates just like that!

                      Comment


                      • #12
                        Re: Merge data to PowerPoint certificates without Word

                        URGENTLY, can someone explain to me again how can I merge records in excel onto a powerpoint certificate but in details please.
                        Thank you!

                        Nicole

                        Comment


                        • #13
                          Re: Merge data to PowerPoint certificates without Word

                          Andy

                          I have just stumbled upon this site, and the Certs code is almost the perfect answer to my problem. I have a large spreadsheet that calculates scores for organisations, based on a web survey I have conducted, and I am trying to automate the production of a multi page report for each organisation. The report containsa mixture of "preset" text, graphs lifted from the Excel spreadsheet and some pages that contain a mixture of "preset" text that also contain data from Excel along the same lines as the Certs example. Is it easy to modify the certs files to allow for the creation of multi page Powerpoint reports. I have attached the certs files as I have tried to adapt them (I get the three certificates being produced, but my second page on the "template" just gets reproduced at the ebd of the certs file without any alteration.

                          Many thanks again for the code
                          Attached Files

                          Comment


                          • #14
                            Re: Merge data to PowerPoint certificates without Word

                            Just letting you know that I am so happy to have found this! Thank you! Six years later you are still making people happy.

                            Comment


                            • #15
                              Re: Merge data to PowerPoint certificates without Word

                              Thanks a lot that what I'm looking for !

                              Comment

                              Working...
                              X