In this article I will explain how you can use VBA for Excel to save a range of cells as a JPEG image. Basically what we do is the following:

1. Create an empty chart in some sheet which is not used.
2. Copy the cells with the required data as a picture.
3. Paste the range onto the chart.
4. Export the table.

Save Range as JPEG:

In this example I will save the range A1:E12 as a JPEG image to the path “D:StuffBusinessTemp”

This can be done using the code below:

Sub Example1()
Dim i  As Integer
Dim intCount As Integer
Dim objPic As Shape
Dim objChart As Chart
'copy the range as an image
Call Sheet1.Range("A1:E12").CopyPicture(xlScreen, xlPicture)

'remove all previous shapes in sheet2
intCount = Sheet2.Shapes.Count
For i = 1 To intCount
    Sheet2.Shapes.Item(1).Delete
Next i
'create an empty chart in sheet2
Sheet2.Shapes.AddChart
'activate sheet2
Sheet2.Activate
'select the shape in sheet2
Sheet2.Shapes.Item(1).Select
Set objChart = ActiveChart
'paste the range into the chart
objChart.Paste
'save the chart as a JPEG
objChart.Export ("D:StuffBusinessTempExample.Jpeg")
End Sub


Result:

The line below copies the range as an image:

Call Sheet1.Range("A1:E12").CopyPicture(xlScreen, xlPicture)


The for i loop below removes any previous shapes and charts in sheet2:

'remove all previous shapes in sheet2
intCount = Sheet2.Shapes.Count
For i = 1 To intCount
    Sheet2.Shapes.Item(1).Delete
Next i


Basically this should be done on an empty sheet to make sure nothing goes wrong.

The lines below add a new chart and assign it the objChart variable:

'create an empty chart in sheet2
Sheet2.Shapes.AddChart
'activate sheet2
Sheet2.Activate
'select the shape in sheet2
Sheet2.Shapes.Item(1).Select
Set objChart = ActiveChart


The line below pastes the range as a picture onto the chart:

objChart.Paste


The line below saves the chart as a JPEG at the address “D:StuffBusinessTemp”:

objChart.Export ("D:StuffBusinessTempExample.Jpeg")


Remove White Spaces From Image:

As you can see from the resulting image in the previous section, there was a lot of empty space around the final image. The image dimensions are based on the dimensions of the chart object . In order to remove those spaces we must modify the dimensions of the chart to match those of the range. This can be done by adding the lines below to our previous code:

Sub Example2()
Dim i  As Integer
Dim intCount As Integer
Dim objPic As Shape
Dim objChart As Chart
'copy the range as an image
Call Sheet1.Range("A1:E12").CopyPicture(xlScreen, xlPicture)

'remove all previous shapes in sheet2
intCount = Sheet2.Shapes.Count
For i = 1 To intCount
    Sheet2.Shapes.Item(1).Delete
Next i
'create an empty chart in sheet2
Sheet2.Shapes.AddChart
'activate sheet2
Sheet2.Activate
'select the shape in sheet2
Sheet2.Shapes.Item(1).Select
Set objChart = ActiveChart
'paste the range into the chart
Sheet2.Shapes.Item(1).Width = Range("A1:E12").Width
Sheet2.Shapes.Item(1).Height = Range("A1:E12").Height
objChart.Paste
'save the chart as a JPEG
objChart.Export ("D:StuffBusinessTempExample.Jpeg")
End Sub


 Result:

Remove Image Border:

As you can from the images from the previous sections a black border was added to the image. This is due to the chart objects border. In order to remove the border, we would need to remove the chart objects border. This can be done by adding the line below:

Sub Example3()
Dim i  As Integer
Dim intCount As Integer
Dim objPic As Shape
Dim objChart As Chart
'copy the range as an image
Call Sheet1.Range("A1:E12").CopyPicture(xlScreen, xlPicture)

'remove all previous shapes in sheet2
intCount = Sheet2.Shapes.Count
For i = 1 To intCount
    Sheet2.Shapes.Item(1).Delete
Next i
'create an empty chart in sheet2
Sheet2.Shapes.AddChart
'activate sheet2
Sheet2.Activate
'select the shape in sheet2
Sheet2.Shapes.Item(1).Select
Set objChart = ActiveChart
'paste the range into the chart

Sheet2.Shapes.Item(1).Line.Visible = msoFalse
Sheet2.Shapes.Item(1).Width = Range("A1:E12").Width
Sheet2.Shapes.Item(1).Height = Range("A1:E12").Height
objChart.Paste
'save the chart as a JPEG
objChart.Export ("D:StuffBusinessTempExample.Jpeg")
End Sub


Result:

You can download the file and code related to this article from the link below:

• Range to Jpeg.xlsm

See also:

• Word VBA, Save Table as JPEG

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com