Jump to content
scbuckeye

Excel Save Range as Image

Recommended Posts

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.

Share this post


Link to post
Share on other sites
water

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
Juvigy

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

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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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

  • Similar Content

    • MrCheese
      By MrCheese
      HI there
      this is driving me nuts - i get the row count, but not the column count - what am I missing? Thanks for your help!
      #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> ; XlDirection enumeration: https://msdn.microsoft.com/en-us/library/office/ff820880.aspxGlobal $oExcel = _Excel_Open() Global $xlup = -4162 Global $xlByRows, $xlPrevious, $xlByColumns Global $oExcel = _Excel_Open() $bookname = "temp.xlsx" $sWorkbook = @ScriptDir & "\" & $bookname Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True) With $oWorkbook.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iColCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Column.Count MsgBox(0, "", "row:" & $iRowCount & "Col:" & $iColCount) EndWith  
      for context - i want to :
      * count columns used in excel
      * create ini file from the rows in each column - finishing at the last column used - i.e. one column for one ini file; containing 15 rows or so.
      is it better to read the entire sheet to an array via the sheettoarray function? then read it from that?
       
       
      temp.xlsx
    • AnonymousX
      By AnonymousX
      Sorry if this is a repost, but having some trouble searching for the answer and pretty tired right now.
      I'm just wondering how to make a border around a range of cells in Excel. I want the regular lines created by selecting "All Borders" option around F2:G3
      I saw this code:
      With $oExcel.ActiveSheet.range("F2:G3") .Select .Borders($xlEdgeBottom).LineStyle = $xlContinuous .Borders($xlEdgeBottom).Weight = $xlThick .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic EndWith but I get errors of these variables not existing. It seems these no longer exist in the "#include <Excel.au3>"
      Is there another include file I need?
      I got this to work for highlighting cells, wondering if there is a option similar to this for all borders?
      $oExcel.ActiveSheet.Range("F2:G3").Interior.ColorIndex = 6 Thanks
    • YouriKamperman
      By YouriKamperman
      I am working on a script that will turn all file names in a directory into an array, and then writes this Array to an Excel file.
      This in itself is working, but the RangeWrite function always puts the Array count in the first cell.
      How can i make sure this does not happen? I can of course just filter it out in Excel, but i am trying to keep all logic of filtering text in my script.
      This is what my script looks like:
      Local $Yesterday = _DateAdd('d', -1, _NowCalcDate())
      Local $cDate = StringReplace($Yesterday, "/", "-")
      Local $aFileList = _FileListToArray(@WorkingDir & "/" & $cDate, "*")
      Local $oExcel = _Excel_Open()
      Local $oWorkbook = _Excel_BookNew($oExcel)
      _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFileList)
    • Eminence
      By Eminence
      Hello,
      Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable?
      Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array.
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended) ; Open Excel Woorkbook and return object Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True) If @error Then MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf Local $aResult = _Excel_RangeRead($oWorkbook) ; Error Trapping If @error Then MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf _ArrayDisplay($aResult) My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable. 
       
      Any help is appreciated. Thanks in advance.
    • JNutt
      By JNutt
      I am trying to close an excel file that was not opened with _Excel_Open.  How do I found the excel application object?  I'm new and I am used to files and folders names, so an 'object' is new to me.  I have the info too and simply spy, but I don't know which info is the object name/string.  In the example from help doc's I see the code below and I tried justin pasting it into Scite.
      <
      Local $oExcel1 = ObjCreate("Excel.Application")
      ; Close the Excel instance which was not opened by _Excel_Open
      ; (will still be running because it was not opened by _Excel_Open)
      _Excel_Close($oExcel1)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      Sleep(2000)
      Local $aProcesses = ProcessList("Excel.exe")
      MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Function ended successfully." & @CRLF & @CRLF & $aProcesses[0][0] & " Excel instance(s) still running.")
      >
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.