I created VBA that create a slide for each row I have in the excel all the English data is good and even in the Arabic name reflected with no issue, but in the date I got the month like this áÇíÑ and the numbers are still in english
here’s the code:
Sub GenerateCertificatesWithFormatting()
Dim pptSlide As Slide
Dim pptShape As Shape
Dim excelApp As Object
Dim workbook As Object
Dim sheet As Object
Dim rowCount As Long
Dim i As Long
Dim newSlides As SlideRange
Dim textRange As textRange
Dim findRange As textRange
Dim arabicLocale As String
arabicLocale = "ar-SA" ' Saudi Arabia locale for Arabic
' Open Excel
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = False ' Keep Excel hidden
Set workbook = excelApp.Workbooks.Open("C:Book1.xlsx") ' Update the path
Set sheet = workbook.Sheets(1)
' Get the number of rows with data in Column A (assuming column A has data)
rowCount = sheet.Cells(sheet.Rows.Count, 1).End(-4162).Row ' xlUp
' Loop through each row in Excel starting from row 2 (skip header)
For i = 2 To rowCount
' Duplicate the first slide as a template
Set newSlides = ActivePresentation.Slides(1).Duplicate
Set pptSlide = newSlides(1) ' Get the new duplicated slide
' Loop through all shapes in the duplicated slide
For Each pptShape In pptSlide.Shapes
If pptShape.HasTextFrame Then
If pptShape.TextFrame.HasText Then
Set textRange = pptShape.TextFrame.textRange
' Replace {Name}
Dim nameValue As String
nameValue = sheet.Cells(i, 1).Value
Set findRange = textRange.Replace("{Name}", nameValue, , False, False)
If Not findRange Is Nothing Then
' Set font style and color for {Name}
With findRange.Font
.Name = "Arial Rounded MT Bold" ' Set the font name
.Size = 24 ' Set the font size
.Bold = True ' Make it bold
.Color.RGB = RGB(102, 0, 51) ' Set font color (Dark Red)
End With
End If
' Replace {Pro}
Dim proValue As String
proValue = sheet.Cells(i, 4).Value
Set findRange = textRange.Replace("{Pro}", proValue, , False, False)
If Not findRange Is Nothing Then
' Set font style and color for {Pro}
With findRange.Font
.Name = "Stencil"
.Size = 23
.Italic = True
.Color.RGB = RGB(236, 99, 55) ' Red color
End With
End If
' Replace {Start}
Dim startDate As String
If IsDate(sheet.Cells(i, 8).Value) Then
startDate = Format(sheet.Cells(i, 8).Value, "[$-ar-SA]d-mmm-yyyy")
Else
startDate = sheet.Cells(i, 8).Value
End If
Set findRange = textRange.Replace("{Start}", startDate, , False, False)
If Not findRange Is Nothing Then
' Set font style and color for {Start}
With findRange.Font
.Name = "Calibri"
.Size = 24
.Bold = True ' Make it bold
.Color.RGB = RGB(0, 0, 0) ' Green color
End With
End If
' Replace {End}
Dim endDate As String
If IsDate(sheet.Cells(i, 9).Value) Then
endDate = Format(sheet.Cells(i, 9).Value, "[$-ar-SA]d-mmm-yyyy")
Else
endDate = sheet.Cells(i, 9).Value
End If
Set findRange = textRange.Replace("{End}", endDate, , False, False)
If Not findRange Is Nothing Then
' Set font style and color for {End}
With findRange.Font
.Name = "Calibri"
.Size = 24
.Bold = True ' Make it bold
.Color.RGB = RGB(0, 0, 0) ' Purple color
End With
End If
End If
End If
Next pptShape
Next i
' Delete the original template slide (slide 1)
ActivePresentation.Slides(1).Delete
' Close Excel
workbook.Close False
excelApp.Quit
Set excelApp = Nothing
MsgBox "NEVADA’s Certificates generated successfully for all rows with formatting!", vbInformation
End Sub
I tried to format the column to be displayed in Arabic date but it failed