In Export Excel Range to a Picture File, I posted a method for exporting an Excel worksheet range to an image on your hard drive. However it suffers from a couple of limitations:
- Filename and path are hardcoded
- Function does not return a value
So I changed the code from a Sub to a Function that returns a boolean value. You specify the range you want to export (as a Range Object) and the filename and path of the image you want to save.
Function ExportRangeToPicture(rng As Excel.Range, img As String) As Boolean ' save a range from Excel as a picture ' rng = Range to export ' img = filename & path ' basic error checking ' check for valid filetypes ' from http://peltiertech.com/WordPress/export-chart-as-image-file/ Const FILE_EXT As String = "gif,png,jpg,jpe,jpeg" If InStr(FILE_EXT, LCase$(Right$(img, 3))) = 0 Then GoTo ExitProc End If ' check for valid path Dim path As String path = Left$(img, InStrRev(img, "\")) If Dir(path, vbDirectory) = "" Then GoTo ExitProc ' check for valid range Dim rRng As Excel.Range On Error Resume Next Set rRng = rng.CurrentRegion On Error GoTo 0 If rRng Is Nothing Then GoTo ExitProc ' check for protected worksheet If ActiveSheet.ProtectContents Then GoTo ExitProc ' copy range to picture, put into chart, export it Application.ScreenUpdating = False rRng.CopyPicture xlScreen, xlPicture Dim cht As Excel.ChartObject Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10) With cht .Chart.Paste .Chart.Export img .Delete End With ' if we got this far, assume success ExportRangeToPicture = True ExitProc: Application.ScreenUpdating = True Set cht = Nothing Set rRng = Nothing End Function
There's some basic error checking, such as checking for valid image file extension and valid ranges. It also makes sure the worksheet isn't protected, because we're adding worksheet objects (temporarily).
Usage:
Sub test()
Dim rng As Excel.Range
Set rng = Range("A1:B10")
If ExportRangeToPicture(rng, "C:\range.gif") Then
MsgBox "ok!"
Else
MsgBox "Didn't work"
End If
End Sub
About JP
29 Comments:
I had to make a slight change to get this to work with Excel 2003. I changed:
to
And then everything worked fine.
Also, I don't think this impacts anything, but in the ExitProc, I think you meant to set rRng to Nothing, not rng.
That's what happens when I cut and paste code. I will fix it now. Thanks.
Hi, great blog.
Instead of exporting the cells to a file on disk, I'd like to copy and paste it (using VBA) to a new Outlook item's body: let's say a task (Dim objTask As Outlook.TaskItem, from your AddToTasks function).
I'm wondering if it's somehow possible and how to do it.
The goal for me would be to be able to select a range, and hit a custom button (or custom right menu entry) to create a new Outlook task which would automatically include a bitmap of the selected cells.
(I'm VBA very-newbie, sorry).
Thanks in advance, best regards
Alex
Alex,
If you want to send a range in the body of an email, you're better off using the technique found at this site:
http://www.rondebruin.nl/mail/folder3/mail4.htm
I did find a thread on Outlook Code that can do what you want, I think. You'll need to combine it with the approach found at Send links via Outlook email
http://www.outlookcode.com/threads.aspx?forumid=1&messageid=28976
Just export the range to the same filename each time, and insert the signature whenever you need the range.
Hi
Just curious, the exported range in gif file is very small. Can we control the image size please?
Regards
KC
KC,
You can either increase the range size, or play with the width and height options in line 34.
Hi,
this trick how to "copyPaste" picture into image file is perfect and many thanx for this column. but!!!
I were solving 3 problems, 1st pore quality of target image, 2nd size is smaller than original and 3rd some strange cropping/cutting of target image.
Solution is simple but not so hard as i expected.
I found that .CopyPicture method is necessary to use with parameter Format:=xlBitmap. This is problem with previous setting of ScreenUpdating=False. xlBitmap cant for some reason push image into clipboard. so change it to TRUE!
Now you got in clipboard high resolution image.
3rd problem raised when you try to save image into some image format. computed size of graph is now for some reason smaller then saved image. I found, that necessary is to put "focus" on chart. i used .Activate method of Chart object, after this you can export in high quality and then delete
Code:
….
Application.ScreenUpdating = True
ActiveWorkbook.Worksheets("…").range("…").CopyPicture xlScreen, xlBitmap
Set oChart = .ChartObjects.Add(0, 0, _
ActiveWorkbook.Worksheets("…").range("…").Width + 10, _
ActiveWorkbook.Worksheets("…").range("…").Height + 10 _
)
With oChart
.Chart.Paste
.Activate
.Chart.Export ("….\chartPic.jpg")
.Delete
End With
….
Hope this will help
Cheers,
Ondrej
Hi
Thank you for the speedy response.
I did adjust line 34, the chart area was big but the actual range remained small.
Increase the range size did not help either. The individual cells were still too small to be visible. The only hope I tried was to increase the fonts in all the cells to at least size 18.
Hello I'm a newbie, i have similar case with KC, i tried the function and the white chart area is bigger than copied cell range , and the copied cell range only visible 10 % of actual height, the rest invisible… what's wrong? I dont know which line should be fix. I use excel 2003…thank you in advance!
Yupe..I just read on help menu in Excel about CurrentRegion it said The current region is a range bounded by any combination of blank rows and blank columns. Read-only.
Because I have blank rows, so the range was not entirely copied into chart. And I have to manipulate it.(by enter blank rows with any character and paint it in white
LoL) Now it works fine… 
But..still im not satisfied..is there any way how can the cell range can be copied even there are many blank rows?
I'm having trouble duplicating your issue.
I opened a new workbook and filled cells A1:E15 with random numbers (highlight A1:E15, type "=RAND()" and press Ctrl-Enter, then paste values). In a standard VBA module I pasted in both procedures above, and adjusted the range in the test procedure accordingly. The workbook is at 100% zoom with 10 point font.
After running the test procedure I have a GIF file image of that range exactly as it appeared on the worksheet, with a bit of whitespace around the edges. I used IrfanView and ACDSee to view the image.
Even expanding the range to A1:O100 didn't make a difference. All I had to do was use the Zoom feature in my image viewer, and the image was viewable.
If you're doing something different, you'll have to give me more details about what range you're trying to export, what its contents are, what your Excel settings are, and so on.
If the CurrentRegion part is causing a problem, simply take it out. Just use "Set rRng = rng"
thank you… by use Set rRng=rng it works well, the picture of exported range become visible 100%, even there are blank rows on sheet.
the exported sheet range contain letters, image, sheet forms, etc, with some rows blank. Very Nice! I like it.
I also add :
vFile = Application.GetSaveAsFilename(InitialFileName:="", filefilter:="JPEG Format (*.jpg),*.jpg")
Set rng = Worksheets("calculation").Range("A1:E68")
If ExportRangeToPicture(rng, vFile) Then
MsgBox "ok!"
Else
MsgBox "Didn't work"
End If
It works ….
Thank you…
I have found the source of my difficulty now.
I must change
Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)
to rRng
This looks like just the method I've been searching for. However, I keep getting a blank/white object irrespective of the range or image format selected? Any thoughts?
Hmmm… seems issue was trying to paste directly into the chart. Worked once I made an intermediate picture object, then pasted that, but seems cumbersome. Using '07. Thanks, Jeff
Unfortunately that approach is required. You could always write additional code that pastes the picture into the chart.
hi everyone. Is there a way to set the imgae to 100% instead of writing down speicifically the size?
Redux:
I find your method (Redux Method for converting excell cells into images), EXCELLENT.
Your method:
- Is fast.
- It works form the very beginning.
- It covvers a wide range of formats.
- It is a jewel in the source code world.
Great job.
This is saving a vector file in content.mso in my temporary internet files. Each file is 7 megs. Is there a way to stop this?
Sorry, no idea, and I can't find anything about this issue. All the macro is doing is creating an image file in the location you specify. I'm not sure what a "vector file" is or what "content.mso" means.
The vector file is an .emf file type. It's being saved into the content.mso folder within temporary internet files. What's even more strange – it's hiding the folder. I had to use a hard drive analysis tool (http://www.foldersizes.com/) to find the files. Once I found them, it still wouldn't let me view the directory. I unhid everything and it still didn't show up. I had to delete them from within the analysis tool. That might be why you're not seeing them.
I ran the code successfully and can't duplicate your issue.
I'm using Office 2003 on Windows Vista. The path to my temp internet files is
C:\Users\Jimmy Pena\AppData\Local\Microsoft\Windows\Temporary Internet Files\.
I stepped through the code and checked the folder size after each instruction. There were no files or folders added (hidden or otherwise) at any time during or after code execution.
I also ran the code in Excel 2002 in my Windows XP machine. The temp internet files folder is
C:\Documents and Settings\Jimmy Pena\Local Settings\Temporary Internet Files\
The result was the same. There were no files or folders added during or after the execution of the function.
Has anyone gotten this error:
runtime error 1004 method export of object chart failed
I get it for charts bigger than 600 Rows with GIF.
i got the same error with 100 rows only. i have office 2003 and 2007 installed. it worked for 2003 only but didn't for the pc with both even if i open with 2003 only.
Is there any way to export an Excel range to a picture without using COPY/PASTE? I don't want the Excel range to show up on the clipboard.
Hi guys, i love C programming but when it come to VBA i do not really know much. It there is any tutorial that will give me foundation on how to begin it will be of grate help.
Secondly, i was given a task to the font size and text style of an email of a range of cell sent from excel through outlook. How can this be done.
Note: Do not really know VBA that well but love to learn
hello- I was wondering if it is possible to create a script that would copy individual cells. Almost a loop of the current script but for every cell that has data.
For example, if I wanted every cell in column B as it's own image file. Is there a way to make the script loop through all cells in column B with data?
I would love to hear your thoughts on if this is possible.
Note to others:
I needed to export a specific part of each worksheet and found that the function was exporting the entire worksheet. What I did was to overwrite this line:
With
Set rRng = Range("Q6:U60")Note: It may have been something to do with my select/activating each worksheet as a loop through the workbook.
In the procedure 'test' you can set this line before declaring the range:
This will ensure that gridlines do not get exported.