Home > Uncategorized > Improving the quality of Excel image export

Improving the quality of Excel image export

As part of a BI solution I’m developing I need to export ranges and PivotTables to image files for inclusion in emails and on webpages.

There are many code snippets out there on the web that allow you to do this by copying the range to a new chart and then exporting the chart as an image (Excel won’t allow you to export to an image directly).  These work fine, but the quality isn’t all that great. 

Group

The trick is to use ‘.CopyPicture xlScreen xlPicture’, then you can resize your chart and export a larger image with a better resolution.  xlPicture copies the range as a vector/metafile image so keeps the quality when resizing (as Jan Peltier explains here).

Function PNGRange(oRng As Range, fName As String) As Boolean

'Function to create a PNG from a selected range or pivottable

Dim chtNm As String

oRng.CopyPicture xlScreen, xlPicture 'Use xlPicture to copy as vector 

Sheets.Add
chtNm = ActiveSheet.Name

'Create a chart on the new sheet and resize it to the dimensions of your copied range.
With ActiveSheet
.Shapes.AddChart.Select
.Shapes("Chart 1").Width = oRng.Width
.Shapes("Chart 1").Height = oRng.Height
End With

ActiveChart.Paste

'This sorts out the quality by making the pasted image larger before exporting.  Change the value from 3 to whatever you like. 1 will not improve quality, 10 will make the output filesize huge.

With ActiveSheet
.Shapes("Chart 1").ScaleWidth 3, msoFalse, msoScaleFromTopLeft
.Shapes("Chart 1").ScaleHeight 3, msoFalse, msoScaleFromTopLeft
End With

'You can export to any image format here.  Ensure the file extension matches the filtername.
ActiveChart.Export Filename:=fName, filtername:="PNG"

Application.DisplayAlerts = False
Sheets(chtNm).Delete
Application.DisplayAlerts = True

PNGRange = True

End Function

As you can see below, the image quality is much improved now.

Group2

 

Advertisements
Categories: Uncategorized
  1. Maurizio Casalaspro
    July 15, 2016 at 3:06 pm

    Awesom!

  2. Andrew
    September 15, 2016 at 11:57 am

    Brilliant stuff Dude! Works like a charm!!

  3. Jordan Almeida
    August 3, 2017 at 10:38 pm

    Thanks a lot!!!!!

  4. November 27, 2017 at 2:43 pm

    Thanks a lot, just what I needed 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: