How to Convert Excel Cell Values Into Individual PNG Files

Question

This question was recently posed, however, it was closed for lack of effort. I spent enough time developing and studying this that I thought it was appropriate to post the answer (see below). Regarding PNG files by cell, there is no clear response to this query.

Example of layout

Desired Output Of Each Word.

Answer 1

This will:

1. create a chart
2. add an image to the chart
3. Loop through each cell in the specified range that is also in the used range
4. For cells that are not empty, it will update the image in the chart.
5. Saves chart as a PNG file.

I got some help from SpreadSheetGuru as well as the legendary Tim Williams on this.

You'll need to update the Const to make your ranges.

Sub buildPNG()
    Const thePath As String = "C:\Users\SRide\OneDrive\Documents\Junk\" 'or wherever
    Const zWidth As Long = 600
    Const zLength As Long = 400
    Const theFontSize As Long = 96
    Const theRange As String = "A:A"
    
Dim WS As Worksheet, aCell As Range
    Set WS = ActiveSheet 'or whatever
    

Dim myChart As ChartObject
        Set myChart = WS.ChartObjects.Add(Left:=50, Width:=zWidth, Top:=50, Height:=zLength)
        
Dim myShape As Shape
    myChart.Activate
    Set myShape = ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, zWidth, zLength)
    
    With myChart.ShapeRange
      .Fill.Visible = msoFalse
      .Line.Visible = msoFalse
    End With

    With myShape.TextFrame
       
        .HorizontalAlignment = xlHAlignCenter
        .VerticalAlignment = xlVAlignCenter
        .Characters.Font.Size = theFontSize

        For Each aCell In Intersect(WS.UsedRange, WS.Range(theRange)).Cells
            If Not IsEmpty(aCell) Then
                .Characters.Text = aCell.Value2
                myChart.Chart.Export (thePath & aCell.Row & ".PNG")
            End If
        Next aCell
    End With
  
End Sub