Export Excel Range to a Picture File
November 13, 2008 • JP • 20 Comments • Rate This Article
• Links to this article
(See 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.
' 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:
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:
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.
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!
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Shipment Tracking Userform for batch processing
Next Post: Take advantage of Intellisense when writing late bound code
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.
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.
I was looking for this for a long time. Thank you very much for this. you are a star…..
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.
Try adapting the code found here:
http://vb.net-informations.com/excel-2007/vb.net_excel_chart_export.htm
HTH
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.
I'm confused about what the problem is. Is it just the path syntax? I assumed the problem was the method call.
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"
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.
It works, thanks
jack
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
Is there a way to avoid the chart boundary coming up in the image. I just want to see the image of the selected Range. When we create a chart object, it adds its own boundary to the image. Is there a way to avoid it?
Sapan -
Format the chart so that it has no border, and either a white background (the default) or a transparent background,
Is there a macro by which i can change the chart settings?
Record a macro while changing the settings, then use this syntax to modify your code.
Sending the picture as an attached file is fine, but I would like to copy the image in Excel and paste it (as a viewable picture) into the body of the outlook message.
What code can be used instead of ".Attachments.Add "c:\myfile.gif" ?
(Good at Excel VBA, suck at Outlook)
oops – found it. Ron deBruin has a good writeup at http://msdn.microsoft.com/en-us/library/ff519602(office.11).aspx
How do you position the picture on the PowerPoint slide once you have pasted it?
Sorry, no idea. Are you looking for code to do this? Manually you can just click and drag it anywhere.