Jump to content

Excel Save Range as Image


 Share

Recommended Posts

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.

Link to comment
Share on other sites

To sum it up:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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)

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 months later...

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?

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...