Export Range As Picture Excel VBA Macro

In this post we see how to export or save a range with data as a picture. This can be useful to share certain data, table, pivot, etc, as an image or paste it in some report. The macro copies the target range as a picture and pastes it into an empty chart of similar dimensions. Microsoft Excel allows to export a chart shape as a picture, hence using that object. Finally, the chart shape is deleted from the sheet. The picture is stored in the specified location with the given name and extension.

Macro code:

 
  Sub SaveRngAsPic()
    
  Dim tempChart As ChartObject, picFolder As String, picName As String, picFileName As String
 
  With Selection  'or specific range with Range("A1:Xy")
      .CopyPicture xlScreen, xlPicture  'copy the range as a picture
      Set tempChart = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height)
  End With
 
  picFolder = ThisWorkbook.Path  'or specific path "C:/Users/username/pictures..."
  picName = "example.png"  'update as needed
  picFileName = picFolder & Application.PathSeparator & picName
 
  With tempChart
      .Activate
      .Chart.Paste
      .Chart.Export picFileName
      .Delete
  End With
 
  End Sub
 

Macro explained:

• First we declare a ChartObject variable to store the embedded chart where the picture is copied temporarily. We also declare some string variables to store the name of the folder and picture file.
• Then we target the range with data to be exported or saved as a picture. In the example above, the Selection is used. That refers to any range or cell selected in the active sheet. Alternatively, we could target a specific range. For example, we can target the used range (ActiveSheet.UsedRange), a specific range, etc. See more about referencing a range here.
• Now we copy the range as a picture with CopyPicture. This is a method of the Range object and accepts two parameters: the appearance and the format. The appearance can either be xlPrinter or xlScreen, the latest being a better choice to resemble the display on the screen as close as possible. The format can be xlBitmap (bitmap format) or xlPicture (vector format). The picture can be saved as jpg, png, and a few other formats.
• We add an empty embedded chart in the active sheet that spans the size of the range. Therefore, the position and dimensions of the chart are given by the left, top, width, and height properties of the selected range.
• The variables picFolder and picName store the directory and name of the picture respectively. In the example above, we assign the same path where the Excel workbook is saved (ThisWorkbook.Path). But that can be any other folder on the system or a shared drive, for example. The variable picFileName concatenates both path and picture name separated by a path separator that is usually the back slash.
• Now we activate the temporary chart and paste the selected range previously copied as a picture into the chart area. This object is used instead of a picture/shape object or other type of object because it is the only one that can be exported as a picture in Excel.
• Finally, we export the chart along the picture to the specified location and with the given picture name and extension. After that we delete the temporary chart from the sheet unless we want to keep it there for some reason.

We can also open the picture once saved with the line of code below. That opens the exported picture with the default image viewer program on your system.

 ThisWorkbook.FollowHyperlink picFileName

 

This is how we Export A Range As Picture in Excel using VBA macros.

Other examples:

• How to Sort Data With Custom List with Excel VBA macros
• How to Add Slicers To Table Headers with Excel VBA macros
• How to Add Consecutive Numbers with Excel VBA macros