Sign in to follow this  
Followers 0
mattschinkel

Excel Chart

2 posts in this topic

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

#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.

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.