Jump to content

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

Excel Charts


  • Please log in to reply
33 replies to this topic
peterm
  • Members
  • 60 posts
  • Last active: Jul 04 2013 05:24 PM
  • Joined: 25 Jul 2006

Fails on my XP system with Excel 2002 SP3.

Gets stuck at

XL.ActiveSheet.Shapes.AddChart.Select

 

Not sure if that is my version of AHK 1.1.08.01 or an old Excel version

 

Peterm



Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011
This is awesome, thanks for sharing!

 

 

No problem!

 

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

 

 

Perfect, sounds good!

 

Fails on my XP system with Excel 2002 SP3.

Gets stuck at

XL.ActiveSheet.Shapes.AddChart.Select

 

Not sure if that is my version of AHK 1.1.08.01 or an old Excel version

 

Peterm

 

 

I ran it on XP with Excel 2010 the other day.  The earlier versions of Excel don't have the same graphing options.  I believe it should work on any versions from 2007 onwards. If you want to use an earlier version, use the macro recorder and create a chart, then translate the VBA code.



krismase
  • Members
  • 19 posts
  • Last active: Feb 28 2013 09:03 PM
  • Joined: 07 Jan 2009

Unreal....Thank you!



nemezisx
  • Members
  • 6 posts
  • Last active: Apr 07 2017 12:18 PM
  • Joined: 28 May 2011

Brother, please tell me where i can get the value of XL.ActiveChart.ChartType, XL.ActiveChart.ChartStyle, dan XL.ActiveChart.ApplyLayout. I want to use 2Dchart on my script. Sorry if this question is not important. sad.png

translated by google translate.



Xx7
  • Members
  • 674 posts
  • Last active: Mar 24 2015 10:48 PM
  • Joined: 19 Apr 2011

Brother, please tell me where i can get the value of XL.ActiveChart.ChartType, XL.ActiveChart.ChartStyle, dan XL.ActiveChart.ApplyLayout. I want to use 2Dchart on my script. Sorry if this question is not important. sad.png

translated by google translate.

 

The easiest way to get those values is by using the Excel macro recorder.  Create the Chart you want, then look at the VBA code.

 

Your VBA code will look something like this:

    Range("A1:C5").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineStacked100
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$C$5")
    ActiveChart.ApplyLayout (3)
 

 

Look up the ChartType in the list below... "xlLineStacked100 = 64"

 

AHK code for the ChartType and ApplyLayout lines:

XL.ActiveChart.ChartType := 64
XL.ActiveChart.ApplyLayout(3)

 

Some information on ChartStyle and ApplyLayout

http://www.itechtalk...thread1172.html

 

List of constants for ChartType

http://msdn.microsof...office/bb241008(v=office.12).aspx

 

xl3DArea    -4098
xl3DAreaStacked    78
xl3DAreaStacked100    79
xl3DBarClustered    60
xl3DBarStacked    61
xl3DBarStacked100    62
xl3DColumn    -4100
xl3DColumnClustered    54
xl3DColumnStacked    55
xl3DColumnStacked100    56
xl3DLine    -4101
xl3DPie    -4102
xl3DPieExploded    70
xlArea    1
xlAreaStacked    76
xlAreaStacked100    77
xlBarClustered    57
xlBarOfPie    71
xlBarStacked    58
xlBarStacked100    59
xlBubble    15
xlBubble3DEffect    87
xlColumnClustered    51
xlColumnStacked    52
xlColumnStacked100    53
xlConeBarClustered    102
xlConeBarStacked    103
xlConeBarStacked100    104
xlConeCol    105
xlConeColClustered    95
xlConeColStacked    96
xlConeColStacked100    97
xlCylinderCol    98
xlCylinderColClustered    92
xlCylinderColStacked    93
xlCylinderColStacked100    94
xlDoughnut    -4120
xlDoughnutExploded    80
xlLine    4
xlLineMarkers    65
xlLineMarkersStacked    66
xlLineMarkersStacked100    67
xlLineStacked    63
xlLineStacked100    64
xlPie    5
xlPieExploded    69
xlPieOfPie    68
xlPyramidBarClustered    109
xlPyramidBarStacked    110
xlPyramidBarStacked100    111
xlPyramidCol    112
xlPyramidColClustered    106
xlPyramidColStacked    107
xlPyramidColStacked100    108
xlRadar    -4151
xlRadarFilled    82
xlRadarMarkers    81
xlStockHLC    88
xlStockOHLC    89
xlStockVHLC    90
xlStockVOHLC    91
xlSurface    83
xlSurfaceTopView    85
xlSurfaceTopViewWireframe    86
xlSurfaceWireframe    84
xlXYScatter    -4169
xlXYScatterLines    74
xlXYScatterLinesNoMarkers    75
xlXYScatterSmooth    72
xlXYScatterSmoothNoMarkers    73
 

 

Hope this helps! happy.png



nemezisx
  • Members
  • 6 posts
  • Last active: Apr 07 2017 12:18 PM
  • Joined: 28 May 2011
wow its help me, thank bro.

Guest10
  • Members
  • 1216 posts
  • Last active: Oct 30 2015 05:12 PM
  • Joined: 27 Oct 2012

Gets stuck at XL.ActiveSheet.Shapes.AddChart.Select confused.png

 

How can I run this on an older Excel?

 

Recorded Macro is as follows:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/9/2013
'

'
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B4"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub


tomjuggler
  • Members
  • 12 posts
  • Last active: Dec 04 2014 09:37 PM
  • Joined: 17 Feb 2011

I know this is going to come in useful someday. Thanks for sharing.

 

Was thinking of making a gui version for fun, so you can choose options and values such as 45 apples, 32 pears, 22 banannas and then get a pie chart. 



tomjuggler
  • Members
  • 12 posts
  • Last active: Dec 04 2014 09:37 PM
  • Joined: 17 Feb 2011

Ok just went to the bar chart link above, also pretty cool



Carcophan
  • Members
  • 1578 posts
  • Last active: Nov 27 2013 06:46 PM
  • Joined: 24 Dec 2008

Awesome work.

 

 

I was unable to get the results from your images to display on the GUI itself.

 

I dug around, and the .bmp that is saves IS correct, X and Y axis info and colors as expected, however that info is lost when the GUI loads the image file. 

I only see the lines/bars, no header info or background colors etc.  So the chart script is working but the GUI doesnt just display the image.

 

 

EDIT:

RESOLVED THE ISSUE! :)

Changing the .bmp to .jpg solved the issue for me specifically.



hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

Pretty cool script
 
 I run it and show this :
 
 
---------------------------
New AutoHotkey Script (6).ahk
---------------------------
Error:  0x8002000B - Invalid index.
 
Source: (null)
Description: (null)
HelpFile: (null)
HelpContext: 0
 
Specifically: Worksheets

 
Line#
012: XL.Range("B4").Value := 21  
014: XL.Range("A1:B4").Select  
015: XL.ActiveSheet.Shapes.AddChart.Select  
016: XL.ActiveChart.ChartType := 63  
017: XL.ActiveChart.PlotArea.Select  
018: XL.ActiveChart.SetElement(207)  
019: XL.Range("A1").Select  
---> 021: XL.Worksheets("Sheet1").ChartObjects(1).Chart.Export("C:\pic1.bmp")  
023: XL.ActiveWorkbook.Close(0)  
024: XL.Quit  
026: Gui,Add,Picture,,C:\pic1.bmp
027: Gui,Show
028: Return
029: Exit
030: Exit
 
Continue running the script?
---------------------------
Yes   No   
---------------------------

 

any Ideas?

 

thanks


Every day exist something new to learn

Carcophan
  • Members
  • 1578 posts
  • Last active: Nov 27 2013 06:46 PM
  • Joined: 24 Dec 2008

Is your harddrive listed as C:\? 

What version of Excel do you have 



hot hot 85
  • Members
  • 72 posts
  • Last active: Sep 29 2015 05:40 AM
  • Joined: 27 Jun 2011

Is your harddrive listed as C:\? 

What version of Excel do you have 

 

I have excel 2010 and I run it on desktop.


Every day exist something new to learn

nemezisx
  • Members
  • 6 posts
  • Last active: Apr 07 2017 12:18 PM
  • Joined: 28 May 2011
try export to drive D or else, some problem couse 'user account control'.

fathom
  • Members
  • 13 posts
  • Last active: Apr 02 2015 04:58 PM
  • Joined: 27 Aug 2012

I found this and thought it would be helpful for anyone using this method to make graphs.

 

These number values are used with the XL.ActiveChart.SetElement() method.  You can do things like move the chart title around, move the data labels in relation to their point, move the legend, etc.

 

http://msdn.microsof...e/ff864118.aspx