Using VBA to change the font size of embedded content (HTML) from Excel to Powerpoint

Question

I am trying to write a code to copy content from an Excel worksheet to Powerpoint (I have no clue of programming or whatsoever and googled everything - my code is probably messed up). I managed to get the content copied to Powerpoint and embed it there as HTML (because I want to be able to edit the copied data in Powerpoint after copying), here is the code:

Sub ExportContent()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)

'open ppt
Set PPTApp = CreateObject("PowerPoint.Application")
PPTApp.Visible = msoTrue
Set pptPres = PPTApp.Presentations.Add(msoTrue)
Set pptslide = pptPres.Slides.Add(Index:=1, Layout:=12)

'copy Excel content
Set rng = ws.Range("A1:G50")
rng.Copy

'copy to ppt
pptslide.Shapes.PasteSpecial DataType:=8
Set excontent = pptslide.Shapes(pptslide.Shapes.Count)

'change font
With excontent.Shapes(1).TextFrame.TextRange.Characters
  .Font.Size = 30
End With

However, I am struggling with the code for changing the font size of the copied content using Excel VBA. In Powerpoint, you can select all content and hit the little arrow up next to font size to increase the font size of everything. So my question is:

1. is it even possible to do that using Excel VBA?
2. and if yes, how? I already tried solutions I found online before (such as this), but I always get compiling errors.

Really thankful for any help!

Answer 1

Sub ExportContent()
' two new variables:
Dim lCol As Long
Dim lRow As Long
' And if you're not already doing so outside this snippet
' you should be declaring ALL of your variables
' Add OPTION EXPLICIT to the top of every module. 
' It'll keep you honest.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)

'open ppt
Set PPTApp = CreateObject("PowerPoint.Application")
PPTApp.Visible = msoTrue
Set pptPres = PPTApp.Presentations.Add(msoTrue)
Set pptslide = pptPres.Slides.Add(Index:=1, Layout:=12)

'copy Excel content
Set rng = ws.Range("A1:G50")
rng.Copy

'copy to ppt
pptslide.Shapes.PasteSpecial DataType:=8
Set excontent = pptslide.Shapes(pptslide.Shapes.Count)

'change font
' The pasted HTML will come into PPT as a table, 
' so you need to treat it as such:
    With excontent.Table
        For lCol = 1 To .Columns.Count
            For lRow = 1 To .Rows.Count
                .Cell(lRow, lCol).Shape.TextFrame.TextRange.Font.Name = "Algerian"
                ' do whatever formatting to the text you want to do here
                ' or work with .Cell(lRow, lCol).Shape to change cell fills etc
            Next
        Next
    End With