(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.
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:
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:
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.
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!
About JP
26 Comments:
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.
I've been using this for a while now on a file with about 15 sheets. Every time I run this macro to export images of each sheet, the file size bloats by about 200kb, which gets very unwieldy very quickly.
Any ideas as to why that happens? Anyone having similar issues?
Thanks
Hi, I am using excel 2007 version with the above macro. it works fine. But excel is getting hanged everytime, I open a fresh. It is taking atleast 15 minutes to open. How to avoid this hanging problem. Please note, there is not data inside the excel. I am just trying to export cells as JPEG file using the above code.
I have no idea. It could be due to an addin, or a hundred other things. I doubt it's related to the macro.
dont work with pivot table ranges, anny suggestion ?
Not really, but I'll try it and see if I can get it to work.
I have created this small macro in order to save a range, contain both graphs, tables and text.
Application.ScreenUpdating = True
Range("B4:S110").Select
Hi = Selection.Height
Wi = Selection.Width
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
Set ch = ActiveSheet.ChartObjects.Add(0, 0, Wi, Hi)
ch.Chart.Paste
ch.Chart.Export Filename:="D:\disco_d\Utilità\UIC518\prova.gif", FilterName:="GIF"
I have created this small macro in order to save a range, contain both graphs, tables and text.
The chart is correctly created, and if I copy & paste on a word sheet everything is correct; but when I export it, the .GIF file contains only the first 72, 73 rows.
I have checked with other ranges and excel sheets, but the .GIF file contains only the first 73 rows also if the chart on excel is complete.
What appends during the export phase?
Thanks Marcello