export excel cell as pic

Get help with using AutoHotkey and its commands and hotkeys
New user

export excel cell as pic

13 Feb 2018, 23:26

Hi Ahk World

I´m trying to export some range of cell as picture , I google it and I found code in VB but I stock to convert in AHK

Any Help would be nice

this is the file
Spoiler
code

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xl := ComObjCreate("Excel.Application")
xl.Visible := true
path = %A_ScriptDir%\A.xlsx
xl.Workbooks.Open(Path)


;xl.Worksheets("Excel_Gauge5").ChartObjects(6).Chart.Export( A_MyDocuments "\pic1.jpg" )



  xl.Application.ScreenUpdating := False
  xl.Set h1 := Sheets("Excel_Gauge5")
    Set h2 = Sheets.Add
    ruta = ThisWorkbook.Path & "\"
    archivo = ruta & h1.[D1] & ".JPEG"
    '
    rango = "P1:U22"
    '
    With h1.Range(rango)
        fi = .Cells(1, 1).Row
        ff = .Rows.Count + fi - 1
        ci = .Cells(1, 1).Column
        cf = .Columns.Count + ci - 1
        izq = .Cells(1, 1).Left
        der = h1.Cells(1, cf + 1).Left
        baj = .Cells(1, 1).Top
        arr = h1.Cells(ff + 1, 1).Top
        anc = der - izq
        alt = arr - baj
    End With
    '
    h1.Range(rango).CopyPicture
    h2.Shapes.AddChart
    With h2.ChartObjects(1)
        .Width = anc
        .Height = alt
        .Chart.Paste
        .Chart.Export archivo
        .Delete
    End With
    Application.DisplayAlerts = False
    h2.Delete
    Application.DisplayAlerts = True
    '
    MsgBox "Celdas guardadas como imagen en el archivo: " & archivo, vbInformation, Date
End Sub




Thanks in advance
User avatar
Jovannb
Posts: 209
Joined: 17 Jun 2014, 02:44
Location: Austria

Re: export excel cell as pic

14 Feb 2018, 02:59

Hi

I would

a) select that cell(s) which you want to export as pic in Excel

b) print that selection e.g. via ghostscript-printer-driver into a postscript file (.ps)

c) convert that to jpg or whatever you want with imagemagick

that (a,b,c) is possible by using AHK and Excel-Com, Ghostscript is free, Imagemagick too

J.B.
AHK-Release 1.1.28.00 Ansi 32-bit, Win10 (1709, 16299.122) 64-bit, german
Ahk New user

Re: export excel cell as pic

14 Feb 2018, 15:52

Hi

So far I have this

xl := ComObjCreate("Excel.Application")
xl.Visible := true
path = %A_ScriptDir%\A.xlsx
xl.Workbooks.Open(Path)

xl.Worksheets("Excel_Gauge5").Range("P1:U22").CopyPicture xlScreen, xlBitmap

xl.Worksheets("Excel_Gauge5").Paste.Destination := xl.Worksheets("Excel_Gauge5").Range("V2")

the problem is the pic is in the clipboard
I don´t know how to save the clipboard as file.jpg

Thanks in advance
User avatar
Jovannb
Posts: 209
Joined: 17 Jun 2014, 02:44
Location: Austria

Re: export excel cell as pic

15 Feb 2018, 01:33

Hi "AHK New User",

I've no idea how to save that picture from clipboard to disk.

My proposal is different, read point b) and c) above

J.B.
AHK-Release 1.1.28.00 Ansi 32-bit, Win10 (1709, 16299.122) 64-bit, german
User avatar
jeeswg
Posts: 5449
Joined: 19 Dec 2016, 01:58
Location: UK

Re: export excel cell as pic

15 Feb 2018, 02:06

- You can use the Gdip library to save image data on the clipboard to a file.

Code: Select all

;[Gdip functions]
;GDI+ standard library 1.45 by tic - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=6517

q:: ;excel - copy cells and output to image file
oXl := ComObjCreate("Excel.Application")
oXl.Visible := -1 ;True
oXl.Workbooks.Add
for oCell in oXl.Range("A1:C3")
	oCell.Value := oCell.Address(0,0)
oXl.Range("A1:C3").Copy

pToken := Gdip_Startup()
pBitmap := Gdip_CreateBitmapFromClipboard()
vPath := A_Desktop "\z excel " A_Now ".png"
Gdip_SaveBitmapToFile(pBitmap, vPath)
Gdip_DisposeImage(pBitmap)
Gdip_Shutdown(pToken)

oXl.ActiveWorkbook.Close(0) ;close without saving
oXl := ""

Run, % vPath
return
FanaticGuru
Posts: 1238
Joined: 30 Sep 2013, 22:25

Re: export excel cell as pic

15 Feb 2018, 02:08

Here is the basics of one way of doing it.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlSheet := xlApp.ActiveSheet
xlArea := xlSheet.Range("B2:C3")
xlArea.CopyPicture
xlChart := xlSheet.ChartObjects.Add(0,0, xlArea.Width, xlArea.Height)
xlChart.Chart.Paste
xlChart.Chart.Export(A_Desktop "\Test\RangePic.png", "PNG")
xlChart.Delete
This copies a range to the clipboard as a picture. Then creates a chart and paste that picture into the chart. Then exports the chart as an image to a file. Then deletes the chart.

The chart is used as a conduit to get the image from the clipboard to a file.

This is effected by zoom, so if the range is zoomed larger on the screen it will be large in the image saved. You would need to do some scaling of the chart before exporting if it was important to always have the image exported at 100%.

There are ways to save an image from the clipboard to a file with DLL calls but this uses Excel to handle it.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
User avatar
jeeswg
Posts: 5449
Joined: 19 Dec 2016, 01:58
Location: UK

Re: export excel cell as pic

24 Feb 2018, 09:59

- This is really great @FanaticGuru, thanks for sharing.
- I tested the technique on my script above, and thought I might as well share the new-look script. The borders, in the image you get using Excel directly, look slightly different. AFAICS the Gdip approach gives you a slightly cleaner image.

Code: Select all

q:: ;excel - output to image file
oXl := ComObjCreate("Excel.Application")
oXl.Visible := -1 ;True
oXl.Workbooks.Add
for oCell in oXl.Range("A1:C3")
	oCell.Value := oCell.Address(0,0)
oRange := oXl.Range("A1:C3")
;oRange := oXl.Range("B2:C3")
oRange.CopyPicture
oChart := oXl.ActiveSheet.ChartObjects.Add(0, 0, oRange.Width, oRange.Height)
oChart.Chart.Paste
vPath := A_Desktop "\z excel " A_Now ".png"
oChart.Chart.Export(vPath, "PNG")
;oChart.Delete
oXl.ActiveWorkbook.Close(0) ;close without saving
oXl := oRange := oChart := ""
Run, % vPath
return
FanaticGuru
Posts: 1238
Joined: 30 Sep 2013, 22:25

Re: export excel cell as pic

24 Feb 2018, 14:42

jeeswg wrote:I tested the technique on my script above, and thought I might as well share the new-look script. The borders, in the image you get using Excel directly, look slightly different. AFAICS the Gdip approach gives you a slightly cleaner image.
I used as many method defaults as I could to keep things simple but I am pretty sure you could play around with settings to improve quality.

I believe you can use vector type scaling to get any level of resolution you want, from what you see on screen to even higher resolution than what you see on screen in Excel.

Maybe I will play around with it but so many things to play around with.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers

Return to “Ask For Help”

Who is online

Users browsing this forum: Albireo, Avastgard, Google [Bot], itz4sking and 39 guests