Excel Chart

[mattschinkel]

Here's an example script to make a nice looking excel chart with autoit. Enjoy!

expandcollapse popup

#include <Excel.au3>
 
;open excel file
$oExcel = _ExcelBookNew()
 
;create data
_ExcelWriteCell($oExcel, "10/01/2011", 1, 1)
_ExcelWriteCell($oExcel, "10/02/2011", 2, 1)
_ExcelWriteCell($oExcel, "10/03/2011", 3, 1)
_ExcelWriteCell($oExcel, "10/04/2011", 4, 1)
_ExcelWriteCell($oExcel, "10/05/2011", 5, 1)
$oExcel.ActiveSheet.Columns(1).AutoFit
_ExcelWriteCell($oExcel, "10", 1, 2)
_ExcelWriteCell($oExcel, "23", 2, 2)
_ExcelWriteCell($oExcel, "15", 3, 2)
_ExcelWriteCell($oExcel, "20", 4, 2)
_ExcelWriteCell($oExcel, "34", 5, 2)
 
;Make the chart
CreateChart($oExcel, 1, "My Title", 74, "A6:I20", "=Sheet1!R1C1:R5C1", "=Sheet1!R1C2:R5C2", "Value 1", "Date", "Stats")
 
;CREATE A CHART
; note: if $DataName and $DataRange are an array, multiple data lines can be drawn on graph.
Func CreateChart(ByRef $oExcel, Byref $Worksheet, ByRef $Title, ByRef $ChartType, ByRef $SizeByCells, ByRef $XValueRange, Byref $DataRange, $DataName, $XTitle, $YTitle)
$PlotBy = 2
$oSheet = $oExcel.Worksheets ($Worksheet)
Local $oChartRange;- The range where you want the chart
Local $oNewChart ;- The ChartObject itself
$oChartRange = $oSheet.Range($SizeByCells)
$oNewChart = $oSheet.ChartObjects.Add($oChartRange.Left, $oChartRange.Top, $oChartRange.Width, $oChartRange.Height)
;--
$oChart = $oNewChart.Chart
$oChart.ChartType = $ChartType
;_ExcelWriteCell($oExcel, '=""', 1, 1) ; A1 must contain something
$oChart.SetSourceData ($oExcel.Worksheets(2).Range("A1:A1"), $PlotBy )
$oChart.HasTitle = 1
$oChart.Axes(1).HasTitle = 1
$oChart.Axes(2).HasTitle = 1
$oChart.Axes(1).AxisTitle.Characters.Text = $XTitle
$oChart.Axes(2).AxisTitle.Characters.Text = $YTitle
$oChart.HasDataTable = 1
$oChart.ChartTitle.Characters.Text = $Title ;- set name of chart
 
If $oChart.SeriesCollection.Count = 0 Then
  $oChart.SeriesCollection.NewSeries
EndIf
  
;give an error "Both $DataRange & $DataName must be same type"
if (IsArray($DataName) And not(IsArray($DataRange))) Or (not(IsArray($DataName)) And IsArray($DataRange)) Then
  MsgBox(4096, "", "Both $DataRange & $DataName must be same type")
EndIf
if IsArray($DataRange) Then
  $oChart.SeriesCollection(1).Delete
  for $i = 1 to 5
   $oChart.SeriesCollection.NewSeries
   with $oChart.SeriesCollection($i)
    .Name = $DataName[$i] ;- set name of values
    .XValues = $XValueRange ; X values ;R=row, C=colunm  
    .Values = $DataRange[$i]
   EndWith
  Next
Else
  with $oChart.SeriesCollection(1)
   .Name = $DataName  ;- set name of values
   .XValues = $XValueRange ; X values ;R=row, C=colunm
   .Values = $DataRange  
  EndWith
EndIf
EndFunc

Matt.

[water]

Unfortunately your script doesn't work for me at all.

When I run your exact example I get

C:\Temp\test2.au3(24,169) : ERROR: CreateChart() previously called with Const or expression on ByRef param(s).
Func CreateChart(ByRef $oExcel, Byref $Worksheet, ByRef $Title, ByRef $ChartType, ByRef $SizeByCells, ByRef $XValueRange, Byref $DataRange, $DataName, $XTitle, $YTitle)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Temp\test2.au3(20,119) : REF: first call to CreateChart().
CreateChart($oExcel, 1, "My Title", 74, "A6:I20", "=Sheet1!R1C1:R5C1", "=Sheet1!R1C2:R5C2", "Value 1", "Date", "Stats")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Temp\test2.au3 - 1 error(s), 0 warning(s)

If I remove all ByRef in the function header I get:

>Running:(3.3.6.1):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "C:\Temp\test2.au3"  
C:\Temp\test2.au3 (35) : ==> The requested action with this object has failed.:
$oChart.SetSourceData ($oExcel.Worksheets(2).Range("A1:A1"), $PlotBy )
$oChart.SetSourceData ($oExcel.Worksheets(2)^ ERROR
->17:55:25 AutoIT3.exe ended.rc:1

Could you please modify the example script so it runs without errors?

I run AutoIt 3.3.6.1 and Office 2010.