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).