Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Merge data to PowerPoint certificates without Word

  1. #1
    Join Date
    20th July 2006
    Location
    Canada
    Posts
    29

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    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


  3. #3
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

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

    PM received ALL ok

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    20th July 2006
    Location
    Canada
    Posts
    29

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: Merge data to PowerPoint certificates without Word

    This is the merge macro in excel
    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Cheers
    Andy


  7. #7
    Join Date
    20th July 2006
    Location
    Canada
    Posts
    29

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    20th July 2006
    Location
    Canada
    Posts
    29

    Re: Merge data to PowerPoint certificates without Word

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: Merge data to PowerPoint certificates without Word

    Thanks for letting us know

    Cheers
    Andy


  10. #10
    Join Date
    25th August 2011
    Posts
    1

    Re: Merge data to PowerPoint certificates without Word

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Merge Data Into Word Table (variable Length)
    By erichpowell in forum Excel and/or Word Help
    Replies: 2
    Last Post: June 13th, 2007, 07:47
  2. Troubleshooting Data Merge to Word Document
    By mulix in forum Excel and/or Word Help
    Replies: 1
    Last Post: July 29th, 2006, 15:52

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno