Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Excel Charts

Started by Xx7 , Dec 22 2012 07:07 PM

  • Please log in to reply
33 replies to this topic
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

Inspired by Learning one's BarChart... Excel Charts creates a graph in Excel, saves the graph as an image and displays it in a GUI.

 

 

yJg66.png

 

**EDIT: originally was a question.. solved below smile.png... working examples from post #2 onwards!**

-tested on AHK_L & Excel 2010 (3D graphs may not work in prior versions)

 

Original question:

How could I save the graph as an image WITHOUT making Excel visible through the whole process.  I don't want the user to see Excel open at all.  I want them to be able to run the script, and then a GUI with their graph will show up.

 


XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add
XL.Visible := true  ;<--- I want this to be false... so Excel never appears

XL.Range("A1").Value := "Sept"
XL.Range("A2").Value := "Oct"
XL.Range("A3").Value := "Nov"
XL.Range("A4").Value := "Dec"

XL.Range("B1").Value := 10
XL.Range("B2").Value := 14
XL.Range("B3").Value := 33
XL.Range("B4").Value := 21

XL.Range("A1:B4").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 63
XL.ActiveChart.PlotArea.Select
XL.ActiveChart.SetElement(207)
XL.Range("A1").Select
return

 

 

Here's the result of running the above script...

gyh2V.png

 

 

I want to save the graph as an image and show it in a GUI... so the user only sees this

MpzMY.png


#1 - Posted 22 December 2012 - 07:07 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

I believe this does it... smile.png

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

 

 

Final code:

XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A1").Value := "Sept"
XL.Range("A2").Value := "Oct"
XL.Range("A3").Value := "Nov"
XL.Range("A4").Value := "Dec"

XL.Range("B1").Value := 10
XL.Range("B2").Value := 14
XL.Range("B3").Value := 33
XL.Range("B4").Value := 21

XL.Range("A1:B4").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 63
XL.ActiveChart.PlotArea.Select
XL.ActiveChart.SetElement(207)
XL.Range("A1").Select

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return

 

7KhKm.png


#2 - Posted 22 December 2012 - 07:33 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

Here's another one for you...  The possibilities are endless since it is based on Excel's graphing

 

92wAS.png

 

 

 

XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A1").Value := "Sept"
XL.Range("A2").Value := "Oct"
XL.Range("A3").Value := "Nov"
XL.Range("A4").Value := "Dec"

XL.Range("B1").Value := 10
XL.Range("B2").Value := 14
XL.Range("B3").Value := 33
XL.Range("B4").Value := 21

XL.Range("C1").Value := 22
XL.Range("C2").Value := 41
XL.Range("C3").Value := 14
XL.Range("C4").Value := 31

XL.Range("A1:C4").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := -4101
XL.ActiveChart.PlotArea.Select
XL.ActiveChart.ChartArea.Select
XL.ActiveChart.SetElement(2)
XL.ActiveChart.ChartTitle.Text := "3D AHK line graph"

XL.ActiveSheet.Shapes("Chart 1").Fill.ForeColor.ObjectThemeColor := 15
XL.ActiveSheet.Shapes("Chart 1").Fill.ForeColor.Brightness := 0.8000000119

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit


Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return

 


#3 - Posted 22 December 2012 - 08:09 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

pFyry.png

 

 

 

XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A1").Value := "Sept"
XL.Range("A2").Value := "Oct"
XL.Range("A3").Value := "Nov"
XL.Range("A4").Value := "Dec"

XL.Range("B1").Value := 10
XL.Range("B2").Value := 14
XL.Range("B3").Value := 33
XL.Range("B4").Value := 21

XL.Range("C1").Value := 22
XL.Range("C2").Value := 41
XL.Range("C3").Value := 14
XL.Range("C4").Value := 31

XL.Range("A1:C4").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := -4100
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartStyle := 42
XL.ActiveChart.ClearToMatchStyle

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return

 

 

Maybe move this to the Scripts forum.. wink.png


#4 - Posted 22 December 2012 - 08:23 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

2ZDpE.png

 

 

 

XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A1").Value := "Sept"
XL.Range("A2").Value := "Oct"
XL.Range("A3").Value := "Nov"
XL.Range("A4").Value := "Dec"

XL.Range("B1").Value := 10
XL.Range("B2").Value := 14
XL.Range("B3").Value := 33
XL.Range("B4").Value := 21

XL.Range("C1").Value := 22
XL.Range("C2").Value := 41
XL.Range("C3").Value := 14
XL.Range("C4").Value := 31

XL.Range("A1:C4").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 104
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartStyle := 45
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.SetElement(1)
XL.ActiveChart.ChartTitle.Text := "Green Cone"

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return

#5 - Posted 22 December 2012 - 08:35 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

auchz.png

 

 

 

XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A2").Value := "Sept"
XL.Range("A3").Value := "Oct"
XL.Range("A4").Value := "Nov"
XL.Range("A5").Value := "Dec"

XL.Range("B1").Value := "Days"
XL.Range("B2").Value := 10
XL.Range("B3").Value := 14
XL.Range("B4").Value := 33
XL.Range("B5").Value := 21

XL.Range("C1").Value := "Temp"
XL.Range("C2").Value := 22
XL.Range("C3").Value := 41
XL.Range("C4").Value := 14
XL.Range("C5").Value := 31

XL.Range("A1:C5").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 78
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartStyle := 40
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.Axes(2).MajorGridlines.Select
XL.ActiveChart.SetElement(1)
XL.ActiveChart.ChartTitle.Text := "Temp & Days"

XL.ActiveSheet.Shapes("Chart 1").Fill.ForeColor.ObjectThemeColor := 14
XL.ActiveSheet.Shapes("Chart 1").Fill.ForeColor.Brightness := -0.150000006

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return

 


#6 - Posted 22 December 2012 - 08:55 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

sXP4E.png

 

 

 

XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A2").Value := "Sept"
XL.Range("A3").Value := "Oct"
XL.Range("A4").Value := "Nov"
XL.Range("A5").Value := "Dec"

XL.Range("B1").Value := "Days"
XL.Range("B2").Value := 10
XL.Range("B3").Value := 14
XL.Range("B4").Value := 33
XL.Range("B5").Value := 21

XL.Range("C1").Value := "Temp"
XL.Range("C2").Value := 22
XL.Range("C3").Value := 41
XL.Range("C4").Value := 14
XL.Range("C5").Value := 31

XL.Range("A1:C5").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 81
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartStyle := 48
XL.ActiveChart.ClearToMatchStyle

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return

 


#7 - Posted 22 December 2012 - 09:06 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

Oz09C.png

 

XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A2").Value := "Sept"
XL.Range("A3").Value := "Oct"
XL.Range("A4").Value := "Nov"
XL.Range("A5").Value := "Dec"

XL.Range("B1").Value := "Temp"
XL.Range("B2").Value := 22
XL.Range("B3").Value := 41
XL.Range("B4").Value := 14
XL.Range("B5").Value := 31

XL.Range("A1:B5").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := -4102
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartStyle := 48
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartTitle.Select
XL.ActiveChart.ChartTitle.Text := "Mmmmmm...   Pie"

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return

#8 - Posted 22 December 2012 - 09:20 PM
guest3456
  • Members
  • 1704 posts
  • Last active: Nov 19 2015 11:58 AM
  • Joined: 10 Mar 2011

indeed, well done, should move to scripts forum

 

must you have Excel installed for this to work?


#9 - Posted 22 December 2012 - 10:39 PM
Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

If changing the size, probably best to do it in Excel to maintain the highest quality.

 



XL.ActiveSheet.Shapes("Chart 1").ScaleWidth(1.3,0,0) ;Magnified at 130%... use ".8" for 80% 
XL.ActiveSheet.Shapes("Chart 1").ScaleHeight(1.3,0,0) ;;Magnified at 130%..


 


Chart at 80% size


rfou6.png


 


Chart magnified at 130%


2roDZ.png


 


 


 


XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A2").Value := "Sept"
XL.Range("A3").Value := "Oct"
XL.Range("A4").Value := "Nov"
XL.Range("A5").Value := "Dec"

XL.Range("B1").Value := 2010
XL.Range("B2").Value := 10
XL.Range("B3").Value := 14
XL.Range("B4").Value := 33
XL.Range("B5").Value := 21

XL.Range("C1").Value := 2011
XL.Range("C2").Value := 22
XL.Range("C3").Value := 41
XL.Range("C4").Value := 14
XL.Range("C5").Value := 31

XL.Range("D1").Value := 2012
XL.Range("D2").Value := 30
XL.Range("D3").Value := 8
XL.Range("D4").Value := 27
XL.Range("D5").Value := 11

XL.Range("A1:D5").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 92
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartStyle := 43
XL.ActiveChart.ClearToMatchStyle

XL.ActiveSheet.ChartObjects("Chart 1").Activate
XL.ActiveChart.SetElement(102)
XL.ActiveChart.SetElement(2)
XL.ActiveChart.ChartTitle.Text := "Blue Tube"

XL.ActiveSheet.ChartObjects("Chart 1").Activate
XL.ActiveSheet.Shapes("Chart 1").ScaleWidth(1.3,0,0)  ;Magnified at 130%... use ".8" for 80% above
XL.ActiveSheet.Shapes("Chart 1").ScaleHeight(1.3,0,0) ;;Magnified at 130%..

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return










#10


 -
Posted 22 December 2012 - 11:02 PM


  


  










 
 







 












Xx7




    

  • 
Members

    • 

  • 
674 posts

    • 



    



    



    

  •  Last active: Mar 24 2015 10:48 PM
    • 

  • Joined: 19 Apr 2011
    • 












 
E9EBO.png


 


 


Download --->  Compiled version


 


FileInstall, C:\BlueSea.png, BlueSea.png, 1  ;include this pic as background image
blue_sea := A_WorkingDir "\BlueSea.png"


XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("A2").Value := "Sept"
XL.Range("A3").Value := "Oct"
XL.Range("A4").Value := "Nov"
XL.Range("A5").Value := "Dec"

XL.Range("B1").Value := "2010"
XL.Range("B2").Value := 10
XL.Range("B3").Value := 14
XL.Range("B4").Value := 33
XL.Range("B5").Value := 21

XL.Range("C1").Value := 2011
XL.Range("C2").Value := 22
XL.Range("C3").Value := 41
XL.Range("C4").Value := 14
XL.Range("C5").Value := 31

XL.Range("D1").Value := 2012
XL.Range("D2").Value := 30
XL.Range("D3").Value := 8
XL.Range("D4").Value := 27
XL.Range("D5").Value := 11

XL.Range("A1:D5").Select
XL.ActiveSheet.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 92
XL.ActiveChart.ClearToMatchStyle
XL.ActiveChart.ChartStyle := 43
XL.ActiveChart.ClearToMatchStyle

XL.ActiveSheet.ChartObjects("Chart 1").Activate
XL.ActiveChart.SetElement(102)
XL.ActiveChart.SetElement(2)
XL.ActiveChart.ChartTitle.Text := "Blue Sea with Pic"

XL.ActiveSheet.ChartObjects("Chart 1").Activate
XL.ActiveSheet.Shapes("Chart 1").ScaleWidth(1.1,0,0)
XL.ActiveSheet.Shapes("Chart 1").ScaleHeight(1.1,0,0)

XL.ActiveSheet.ChartObjects("Chart 1").Activate
XL.ActiveSheet.Shapes("Chart 1").Fill.UserPicture(blue_sea)  ;background pic

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")

XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture, , C:\pic1.bmp
Gui, Show
return










#11


 -
Posted 23 December 2012 - 12:23 AM


  


  










 
 







 












Xx7




    

  • 
Members

    • 

  • 
674 posts

    • 



    



    



    

  •  Last active: Mar 24 2015 10:48 PM
    • 

  • Joined: 19 Apr 2011
    • 












 
indeed, well done, should move to scripts forum

 


must you have Excel installed for this to work?


 


 


Moved & Yes, Excel is required!  Tested on Excel 2010, unsure if the 3D charts will work on older versions.










#12


 -
Posted 23 December 2012 - 04:34 AM


  


  










 
 







 












S0und




    

  • 
Members

    • 

  • 
100 posts

    • 



    



    



    

  •  Last active: Apr 08 2015 10:07 AM
    • 

  • Joined: 16 Feb 2007
    • 












 
This is awesome, thanks for sharing!










#13


 -
Posted 23 December 2012 - 06:51 PM


  


  










 
 







 












Xx7




    

  • 
Members

    • 

  • 
674 posts

    • 



    



    



    

  •  Last active: Mar 24 2015 10:48 PM
    • 

  • Joined: 19 Apr 2011
    • 












 
UQjK1.png


 


 


XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add

XL.Range("B1").Value := "Budget"
XL.Range("C1").Value := "2011"
XL.Range("D1").Value := "2012"

Array_1 := ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

;--------- Chart generated using RANDOM values below ----------
Loop, 12
{
  XL.Range("A" A_Index+1).Value := Array_1[A_Index]
  Random, rand, 5, 60
  XL.Range("B" A_Index+1).Value := rand
  Random, rand, 40, 100
  XL.Range("C" A_Index+1).Value := rand
  Random, rand, 40, 100
  XL.Range("D" A_Index+1).Value := rand
}
;---------------------------------------------------------------


XLAS := XL.ActiveSheet

XL.Range("A1:D13").Select
XLAS.Shapes.AddChart.Select
XL.ActiveChart.ChartType := 4  ;All THREE charts originally set-up as Line Graphs
XL.ActiveChart.ChartStyle := 42  ;Black background template
XL.ActiveChart.ApplyLayout(5)  ;This is the design with boxed numbers on the bottom
XLAS.Shapes("Chart 1").ScaleWidth(1.5,0,0)
XLAS.Shapes("Chart 1").ScaleHeight(1.5,0,0)
XL.ActiveChart.SeriesCollection(1).ChartType := 51  ;Changes the "Budget" chart to Bar Graph

XL.ActiveChart.SeriesCollection(3).Format.Line.ForeColor.RGB := 16711680  ;Changes "2012" to blue line
XL.ActiveChart.SeriesCollection(2).Format.Line.ForeColor.RGB := 65535  ;Changes "2011" to yellow line
;Red 255, Orange 33023,Green 60928, Dark Green 25600... etc.
;Source: http://cloford.com/r...ours/500col.htm

XL.ActiveChart.SeriesCollection(2).Smooth := True  ;makes the junctions smooth instead of sharp corners
XL.ActiveChart.SeriesCollection(3).Smooth := True
XL.ActiveChart.ChartTitle.Text := "Smoothed Bar-Line Chart"
XL.ActiveChart.Axes(2, 1).AxisTitle.Text := "$ 000's"

XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")  ;saves the chart as a pic
XL.ActiveWorkbook.Close(0)
XL.Quit

Gui, Add, Picture,, C:\pic1.bmp  ;shows the pic in the GUI
Gui, Show
return










#14


 -
Posted 24 December 2012 - 09:16 AM


  


  










 
 







 












Learning one




    

  • 
Members

    • 

  • 
1483 posts

    • 



    



    



    

  •  Last active: Jan 02 2016 02:30 PM
    • 

  • Joined: 04 Apr 2009
    • 












 
Good job Xx7! I put a link to your work in my BarChart thread. icon_wink.gif










#15


 -
Posted 24 December 2012 - 05:56 PM


  


  


 
My Website • Recommended: AutoHotkey Unicode 32-bit • Join DropBox, Copy


























Back to Scripts and Functions




 
 
 
 


 


















    

  1. AutoHotkey Community
    2. 

  2. → AutoHotkey
    3. 

  3. → Scripts and Functions
    4.