0

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

4
  • How did you format column? Failed how? TEXT() function will return month in Arabic but numbers are still English. Might be the best you can get. Commented Jan 13 at 20:50
  • Maybe this can help stackoverflow.com/questions/70529920/… Commented Jan 13 at 20:59
  • More about TEXT() stackoverflow.com/questions/32090315/vba-change-date-language Commented Jan 13 at 21:51
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Commented Jan 13 at 23:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.