Using VBA to create certificates in Powerpoint from excel cell values

[originalusername]

Good Morning/Afternoon/Evening Everyone,

As you may already have imagined, VBA has kicked my butt to the point where I needed to bother you guys, so apologies for that.

Summary of effort- I have a pre-made Powerpoint template (graduation certificates for students) that has two fields that need filling for every slide made. The fields themselves are formatted as single-cell tables, as I was led to believe that interacts better with Excel code. The first Powerpoint table (“Table 4” per the selection pane) is for student names, and the second (“Table1”) is for the date they graduate.

The issue I’m running into (that isn’t my complete lack of knowledge/understanding on anything VBA) is that the amount certificates we have to make for each class varies by how many students are in each class. The code I’ve been trying to rework is below, courtesy of Worf on a post many moons ago (https://www.mrexcel.com/forum/excel-questions/643780-vba-code-data-powerpoint-table-not-objects.html). Forgive the comments, I was trying to remind/teach myself what it all meant.

[COLOR=#222222][FONT=Verdana]Sub Attempt_3()[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana]'MakingCertificates…hopefully[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]ActiveSheet.Range("$B$108:$J$158").AutoFilterField:=1, Criteria1:="<>"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]‘Above is the table in question, added this to take away all the potential blank spaces. We don’t have any classes that exceed this many students._ 
Also, B108:J108 is the header row. The concatenated student names to be copied to the Powerpoint are in the J column, everything else is the _ 
data that combines in the J column.[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Dim CMaker1 AsWorksheet, objPPT As Object, nrows%, i%, j%, LastRow%[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]‘I just took this from the original post, don’t really understand what nrows%, i%, or j% are.[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Set CMaker1 =Worksheets("Input Fields")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set objPPT =CreateObject("PowerPoint.Application")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]objPPT.Visible= True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'Code below is filepath, if the document name changes or is moved, this must change[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set objPres =objPPT.presentations.Open(Filepath and PPT name)[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]LastRow =CMaker1.Range("B" & Rows.Count).End(xlUp).Row [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]'I wanted it to count off the B row after the table was shortened, as it doesn’t contain any functions in it.[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]For b = 108 ToLastRow - 2[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    objPres.Slides(1).Duplicate[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    'This part also seems to work, duplicates the correct amount of times, but i do not know what the source cell actually is being based off of, im guessing b108[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    'Update to me: it is off of B108, but B108is the header, so that's neat[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Next[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]For b = 108 ToLastRow - 1[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    For j= 1 To 1[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        objPres.Slides(j).Shapes("Table4").Table.Cell(1, 1).Shape.TextFrame.TextRange.Text = CMaker1.Cells(i +109, 1).Value[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    'This part copies cell A109 for reasons unknown, but doesnt loop to keep copying subsequent cells into the remaining slides. [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Next[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Next[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Set objPres =Nothing[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Set objPPT =Nothing[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

The graduation date isn’t accounted for at all in this code, but it would just be copied over and over from one cell. I will make it populate in cell C106 for ease of explanation. Additionally, we would be remaking the certificates with every class, so a "save" function isn't required.

Any assistance/guidance you guys can provide would be greatly appreciated. After reading through some of the posts on this site, I don’t know how much help I can offer in return- most everything seems like super advanced stuff that exceeds my basic use of 'if' statements, but will chip in where I can.

Thank you all in advance!

 

[Worf]

Welcome to the Board

That would be me.
I will work on your request as soon as possible, busy workweek…

 

[originalusername]

Worf,

Thank you for your quick response, I didn't expect to be answered by the creator of that source code, so that's pretty awesome...I suppose what I'm trying to say is that I'm a fan of your work.

 

[Worf]

o The code below copies the filtered table to another location.
o Test it on a copy of your workbook as it deletes data.
o Tell me if you need further explanations

Sub Attempt_3()
Dim objpres As Presentation, b%, CMaker1 As Worksheet, objPPT As Object
[k100] = ""
[k101].Formula = "=len(b109)>0"
[L:T].ClearContents
[B108:J158].AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[K100:K101], _
CopyToRange:=[L108:T108], Unique:=False
Set CMaker1 = Worksheets("Input Fields")
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objpres = objPPT.Presentations.Open("c:\pub\example.pptx")
For b = 108 To CMaker1.Range("L" & Rows.Count).End(xlUp).Row - 2
    objpres.Slides(1).Duplicate
Next
For b = 1 To objpres.Slides.Count
    objpres.Slides(b).Shapes("Table1").Table.cell(1, 1).shape.TextFrame.TextRange.Text = _
    CStr(CMaker1.[c106])
    objpres.Slides(b).Shapes("Table4").Table.cell(1, 1).shape.TextFrame.TextRange.Text = _
    CMaker1.Cells(108 + b, "t")
Next
Set objpres = Nothing
Set objPPT = Nothing
End Sub

 

[Macropod]

Is there a reason for doing this in PowerPoint, instead of Word where you can do it via mailmerge?

 

[originalusername]

To Worf- It works!!! It made me Dim objpres as Object, butit works!!! Thank you so much, you literally just fixed a problem set we havebeen working on for 2-3 months in less than a day (well, a day plus one minute…reallygood timing on that as well, by the way). Will make it a goal to try tounderstand what all of the code means as time goes on, but I seriously can’tthank you enough.
To Macropod- I tried to look into mailmerge recently(shortly before originally posting) but the people here in charge of formattingthe certificates are hard set in their ways of PowerPoint. I initially tried tosubmit an Excel-created certificate and the manner in which the dataneeded to be manipulated was a little too much. Word was easier to work with,but PP seems to be their go-to (until they get cycled out).

 

[Macropod]

You could, of course, copy your current PowerPoint Slide, then paste it into Word (e.g. into the page header) as an image, adjust the image alignment & text wrapping, connect to Excel, then position your mergefields where required before completing the merge. No programming required and far easier to maintain.