33 replies to this topic

[peterm]

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]

This is awesome, thanks for sharing!

 

 

No problem!

 

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

 

 

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]

Unreal....Thank you!

[nemezisx]

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.

translated by google translate.

[Xx7]

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.

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!

[nemezisx]

wow its help me, thank bro.

[Guest10]

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

 

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]

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]

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

[Carcophan]

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]

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

[Carcophan]

Is your harddrive listed as C:\? 

What version of Excel do you have 

[hot hot 85]

Is your harddrive listed as C:\? 

What version of Excel do you have 

 

I have excel 2010 and I run it on desktop.

[nemezisx]

try export to drive D or else, some problem couse 'user account control'.

[fathom]

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