Jump to content
Sign in to follow this  
mattschinkel

Excel Chart

Recommended Posts

mattschinkel

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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  

  • Similar Content

    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
×