Excel Save Range as Image

[scbuckeye]

I  am in need of exporting a Excel range as an image file.  I have seen some examples online that use the CopyPicture command, then paste the image to a temporary chart, and then export that chart to a file.  Is this the best way to get this accomplished, or is there a simpler way to get the image file saved?

Here is the VBA code I have written that accomplishes this, but I would like to eliminate the need for a Macro:

Sub ExportAsImage()
Dim objPic As Shape
Dim objChart As Chart
Dim i As Integer
Dim intCount As Integer
'copy the range as an image
Call ActiveSheet.Range("A1:D21").CopyPicture(xlScreen, xlPicture)
'remove all previous shapes in the ActiveSheet
intCount = ActiveSheet.Shapes.Count
For i = 1 To intCount
    ActiveSheet.Shapes.Item(1).Delete
Next i
'create an empty chart in the ActiveSheet
ActiveSheet.Shapes.AddChart
'select the shape in the ActiveSheet
ActiveSheet.Shapes.Item(1).Select
ActiveSheet.Shapes.Item(1).Width = Range("A1:D21").Width
ActiveSheet.Shapes.Item(1).Height = Range("A1:D21").Height
Set objChart = ActiveChart
'clear the chart
objChart.ChartArea.ClearContents
'paste the range into the chart
objChart.Paste
'save the chart as a JPEG
objChart.Export ("C:\Test\Test.jpg")
'remove all shapes in the ActiveSheet
intCount = ActiveSheet.Shapes.Count
For i = 1 To intCount
    ActiveSheet.Shapes.Item(1).Delete
Next i
End Sub

I have looked at the ExcelChart UDF, but wasn't quite sure the right direction to go in.

Any help would be greatly appreciated.

[water]

To sum it up:

• You already have a chart object in Range A1:D21?
• You want to save this chart as a JPG?

[scbuckeye]

Sorry, I must have missed the email notification that you had responded.  I don't really want to have to create the chart, but if that is the only way to save the range as a jpeg, then I can do that.  The VBA code I have written creates the chart object -- it doesn't already exist.

[water]

I'm not really sure what you expect to get saved as a jpeg. The cells with data or a chart generated from this data?

[water]

To save the specified cells as a picture you would need something like this (ConsoleWrites just added for debugging reasons).
GDI code taken from KaFu's post:

 

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <ClipBoard.au3>
#include <GDIPlus.au3>

Global $xlScreen = 1 ; The picture is copied to resemble its display on the screen as closely as possible
Global $xlPicture = -4147 ; Drawn picture (.png, .wmf, .mix)
Global $xlBitmap = 2 ; Bitmap (.bmp, .jpg, .gif)

; Create application object
Global $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Create a new workbook
Global $oWorkbook = _Excel_BookNew($oAppl)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Copy range to clipboard as a picture
$oWorkbook.ActiveSheet.Range("A1:D21").CopyPicture($xlScreen, $xlBitmap)
ConsoleWrite(@error & @CRLF)
; Save picture to disk
If Not _ClipBoard_Open(WinGetHandle(AutoItWinGetTitle())) Then MsgBox(0, "", "_ClipBoard_Open failed")
$hClipboardImage = _ClipBoard_GetDataEx($CF_BITMAP)
ConsoleWrite("1: " & $hClipboardImage & @CRLF)
_ClipBoard_Close()

_GDIPlus_Startup()
ConsoleWrite("2: " & @error & @CRLF)
$hBitmap = _GDIPlus_BitmapCreateFromHBITMAP($hClipboardImage)
ConsoleWrite("3: " & @error & "-" & @extended & @CRLF)
$sCLSID = _GDIPlus_EncodersGetCLSID("JPG")
ConsoleWrite("4: " & @error & "-" & @extended & @CRLF)
_GDIPlus_ImageSaveToFileEx($hBitmap, @ScriptDir & "\" & TimerInit() & "_Clipboard_Image.jpg", $sCLSID, 0)
ConsoleWrite("5: " & @error & "-" & @extended & @CRLF)
_GDIPlus_BitmapDispose($hBitmap)
ConsoleWrite("6: " & @error & "-" & @extended & @CRLF)
_GDIPlus_Shutdown()
ConsoleWrite("7: " & @error & "-" & @extended & @CRLF)

 

[scbuckeye]

Thanks Water!  I will try that out.  It seems so simple.  One last thing though, totally unrelated to Excel, but I think you may know the answer.  Once I have this image file, I need to insert it into the body of an email template.  Is there a function for that in OutlookEX?

[Juvigy]

.HTMLBody = $body & "TEST <BR> <BR>TEST2"

.Body = "TEST" & @CRLF & "TEST2"

[water]

Please check _OL_ItemAttachmentAdd.au3. There is an example for an inline image.