Export Excel Range to a Picture File

Posted November 13, 2008 – 9:47 pm by JP in Excel

Hello! If you are new here, you might want to subscribe to the RSS feed to read the latest articles from this blog.

Se my followup post Export Excel Range to a Picture File, Redux for updated VBA.

If you ever needed to capture a range, for pasting into a document or email, you'll need to save it as an image first. If so, you might find this routine useful. It takes a contiguous range, starting in A1, and creates a GIF file with a snapshot of the range.

A temporary, intermediate chart is used, so we can use its Export Method to create the GIF. The chart is then deleted. Through trial and error, I guessed at the appropriate chart size. I'm using Windows XP with a resolution of 1024×768, so you might need to adjust the constants' values as appropriate.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Sub CopyRangeToGIF()

' save a range from Excel as a picture

Dim rng As Excel.Range

Dim cht As Excel.ChartObject

Const strPath As String = "C:\"

Application.ScreenUpdating = False

Set rng = Range("A1").CurrentRegion

rng.CopyPicture xlScreen, xlPicture

Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)

cht.Chart.Paste

cht.Chart.Export strPath & "myfile.gif"

cht.Delete

ExitProc:

Application.ScreenUpdating = True

Set cht = Nothing

Set rng = Nothing

End Sub

This code will only create the picture. You'll probably want to do something with it.

If you want to add it to a Word document, try this:

1
2
3
4
5
6

Dim wrdApp As Object ' Word.Application

Dim wrdDoc As Object ' Word.Document

Set wrdApp = CreateObject("Word.Application")

'wrdApp.Visible = True

Set wrdDoc = wrdApp.Documents.Add

wrdDoc.InlineShapes.AddPicture "C:\myfile.gif"

To send it as an attachment in an Outlook email, try this:

1
2
3
4
5
6
7
8
9
10
11

Dim olApp As Object ' Outlook.Application

Dim Msg As Object ' Outlook.MailItem

Set olApp = CreateObject("Outlook.Application")

Set Msg = olApp.CreateItem(0)

With Msg

.To = "coworker@mycompany.com"

.Body = "Check out this range!"

.Attachments.Add "c:\myfile.gif"

.Send

End With

If you want to send the range in the body of an email, you're better off using the technique described here.

And last but not least, if you wanted the picture in a PowerPoint slide, this code will create a new presentation and insert the picture into a slide. Unlike the code snippets above for Outlook and Word, which use the saved GIF copy, for PowerPoint you'll need to call this code right after executing the Range.CopyPicture Method.

1
2
3
4
5
6
7
8
9

Dim pptApp As Object ' PowerPoint.Application

Dim pptPres As Object ' PowerPoint.Presentation

Dim pptSlide As Object ' PowerPoint.Slide

Set pptApp = CreateObject("PowerPoint.Application")

Set pptPres = pptApp.Presentations.Add

Set pptSlide = pptPres.Slides.Add

pptSlide.Shapes.Paste

[Update 11/14/2008]: CopyRangeToGIF sub was updated based on suggestion from Jon Peltier. No need for complicated calculations!

Tags: chart, ChartObject, copy, export, gif, range

Share or bookmark this post:

If you enjoyed this post, make sure you subscribe to the RSS feed!

Request a post: Send an email to coderequest@codeforexcelandoutlook.com

12 Responses to Export Excel Range to a Picture File
I have a suggestion. This segment (in ChartObjects.Add):

ColumnsRatio * lColsCount, RowsRatio * lRowsCount

assumes equal row heights and column widths. Instead use:

rng.Width, rng.Height

Then add a small amount, a few points, because the chart area needs a small margin to fit within the chart object.

By Jon Peltier on Nov 14, 2008
Works. I assumed that the range was calculated in pixels, while the ChartObject width/height was calculated in points, so I started fiddling with an algorithm to calculate the correct size.

By JP on Nov 14, 2008
I was looking for this for a long time. Thank you very much for this. you are a star…..

By Partha on Mar 1, 2009
I the first part of code you have this line:
cht.Chart.Export strPath & "myfile.gif"

My Excel 2007 will not accept & and I have not found other solutions.

By Guppy on Mar 31, 2009
Try adapting the code found here:

http://vb.net-informations.com/excel-2007/vb.net_excel_chart_export.htm

HTH

By JP on Mar 31, 2009
Thanks, I'll test the solution on the link you give.

Meanwhile I did a workaround by setting the path including a file name:
Const strPath As String = "C:\users\frank\desktop\chart.bmp"
I want to make gif and not bmp, but choosing gif they look horrible/distorted.

By Guppy on Mar 31, 2009
I'm confused about what the problem is. Is it just the path syntax? I assumed the problem was the method call.

By JP on Mar 31, 2009
As I include the file name in the path string I only need
cht.Chart.Export strPath
so I avoid the '&' causing trouble in:
cht.Chart.Export strPath & "myfile.gif"

By Guppy on Mar 31, 2009
I see the problem now. Wordpress had converted the "&" to its HTML equivalent, hence the error. If you just change the "&" to an ampersand "&", the original code should work as written. But your solution works as well.

By JP on Mar 31, 2009
It works, thanks

jack

By jack on Jun 29, 2009
Great tutorial, I will try your way. I only drawing so I will pass your post address to my programmer.
Thanks for sharing.
Regards,
Matt Kolorowanki,
Illustrator

By Kolorowanki on Jun 29, 2009