Create certificates in excel (only, i.e. not mail merge with Word)

Question

Hello folks,

I"ve trawled the internet but cant seem to find a solution to this problem...

I'd like to create certificates for groups of people all from a single spreadsheet rather than using Word. We get multiple groups who share their details in a spreadsheet. I"d like to use that spreadsheet to create certificates based on the names etc provided. As we want to do this easily I'd rather it all down from Excel rather than having a word template which I have to re-link to new spreadsheets every course.

I've seen some videos where this appears to work but not the code to manage it.

Can anyone help or point me in the right directions?

Answer 1

Could you share a test file with some dummy data in it? Then I can have a try on it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.  *Please make sure you have removed any sensitive or private information in the sample file before uploading.

Answer 2

Here's an example of what I'm trying to achieve (I've popped it in Google Drive as the cities policies wont allow One Drive sharing). Basically I want to produce multiple certificates printer out as PDFs with data from the first sheet, i.e. persons name, date and a small comment. Ideally I want this all contained in Excel rather than using a separate Word document and mail merge as I want other team members to be able to do this quickly and easily (many of them are not IT savy and will struggle with the mail merge).

Links here: https://docs.google.com/spreadsheets/d/1soFwhTEbtPtn2djiskJyG5f342NzvBP6/edit?usp=sharing&ouid=101158777497021257321&rtpof=true&sd=true

link

Thanks for having a look!

Answer 3

Hi GT

You may find in this link a copy of your file template with a solution to your problem

https://we.tl/t-pgmXZQc0nA

Please,

On the Data sheet

1. In the yellow cell, enter the path of the folder where you want to store the certificates. Make sure it ends with ""
2. Click the Create Certificates button.

And that's it

3. The macro will open the folder once the certificates are created

They will be saved as the "Person Name-Certificate.pdf"

4. and 5) Please, format and make the changes in the "Cert" template/sheet, in the way you want.

Here is the code used

Sub CreateCertificates()

Dim NameRng As Range

Dim cName As Range

Dim myPath As String

Dim fName As String

Application.ScreenUpdating = False

With Sheets("Data")

    .Activate 

    Set NameRng = .Range(Cells(3, "B"), Cells(Rows.Count, "B").End(xlUp)) 

    myPath = .Range("E1").Value 

    For Each cName In NameRng 

          fName = cName.Value & " " & cName.Offset(0, 1).Value & "-Certificate.pdf" 

    With Sheets("Cert") 

           .Shapes("ShName").TextFrame.Characters.Text = cName.Value & " " & cName.Offset(0, 1).Value 

           .Shapes("ShComment").TextFrame.Characters.Text = cName.Offset(0, 3).Value 

           .Shapes("ShDate").TextFrame.Characters.Text = cName.Offset(0, 2).Value 

           ''' Save the Certificate under the corresponding name 

           .ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPath & fName, \_ 

            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 

    End With 

            cName.Offset(0, 4).Value = "Done!" 

    Next cName 

    Application.ScreenUpdating = True 

    MsgBox "Job Done!" 

    '''Show the Certificates 

    Call Shell("explorer.exe " & myPath, vbNormalFocus) 

End With

End Sub

Do let us know if you need more help

Regards

Jeovany

Answer 4

Could you share the file again please? The file transfer was no longer available on the original site.