export excel cell as pic Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
New user

export excel cell as pic

Post by New user » 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: 268
Joined: 17 Jun 2014, 02:44
Location: Austria

Re: export excel cell as pic

Post by Jovannb » 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: 1.1.37.01 Ansi, 32-Bit; Win10 22H2 64 bit, german

Ahk New user

Re: export excel cell as pic

Post by Ahk New user » 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: 268
Joined: 17 Jun 2014, 02:44
Location: Austria

Re: export excel cell as pic

Post by Jovannb » 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: 1.1.37.01 Ansi, 32-Bit; Win10 22H2 64 bit, german

User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: export excel cell as pic  Topic is solved

Post by jeeswg » 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
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: export excel cell as pic

Post by FanaticGuru » 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
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: export excel cell as pic

Post by jeeswg » 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
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: export excel cell as pic

Post by FanaticGuru » 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
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

stretchymantis
Posts: 10
Joined: 13 Nov 2021, 22:24

Re: export excel cell as pic

Post by stretchymantis » 04 Jul 2022, 16:15

FanaticGuru wrote:
15 Feb 2018, 02:08
All wonderful methods on here, but this stood out as an incredibly creative workaround to keep the whole process within Excel. I'm literally trying to find an excuse to use it lol. :p

Post Reply

Return to “Ask for Help (v1)”