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-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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  

  • Similar Content

    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I need to open excel files that may or may not need a password and finally move the files that needs password to manual queue.
      Is there a fastest way to do this?
       
      PS: I have a huge respect for the rules of this forum. I am not asking assistance to override any security measure. I just need to segregate the files that needs passwords.
    • MrCheese
      By MrCheese
      Hi guys,
      without including everything (unless you want it)
      I am copying data from a table in chrome and wanting to paste it into excel.
      Copying in Chrome works.
      I can paste it into the field i want by emulating goto -> ctrl V:
      WinActivate($dataload) WinWaitActive($dataload) Sleep(500) $oWorkbook1.Sheets("ItemReturn").Activate Sleep(500) $msg = "Measuring Sheet" conwrite() ttips2() Local Const $xlUp = -4162 With $oWorkbook1.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, "B")).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number EndWith $NewStartCell = $iLastCell + 2 $msg = "moving to location" conwrite() ttips2() Sleep(250) Send("^g") WinWait("Go To") Sleep(100) Send("B" & $NewStartCell) Sleep(100) Send("{ENTER}") Sleep(500) Send("^v")  
      But, I want to use _excel_rangecopypaste, pasting from the clipboard
      _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, default, "B" & $NewStartCell,default,$xlPasteValuesAndNumberFormats) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error pasting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended) however, this gives me error 4 , extended@:  -2147352567
      How can i fix this or find out how to debug this error?
       
      Thanks
    • Simpel
      By Simpel
      Hi.
      I try to figure out who is using a excel workbook which I can only open "read only". I use this code:
      #include <Array.au3> #include <Excel.au3> Local $sFile = ; excel file with path on a network drive Local $oExcel = _Excel_Open(True, True) Local $oTabelle = _Excel_BookOpen($oExcel, $sFile) Local $aUsers If IsObj($oTabelle) Then $aUsers = $oTabelle.UserStatus _ArrayDisplay($aUsers) EndIf If I am the one allowed to write to the excel file (I'm the first one who opened it) then I will get an array with myself:

      If my collegue opened the excel file first and I run the code I get the following error message:
      "H:\_Conrad lokal\Downloads\AutoIt3\_COX\Tests\test.au3" (9) : ==> The requested action with this object has failed.: $aUsers = $oTabelle.UserStatus $aUsers = $oTabelle^ ERROR The excel file is on a network drive. Is that's the problem?
      Regards, Conrad
    • robertocm
      By robertocm
      change linked image paths in excel 2007 Open XML Files with AutoIt and 7-zip:
      #include <File.au3> ;Change this Local $sFind = "C:\Users\MyUserName\Documents\MyImageFolder\My%20Image1.png" Local $sReplace = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder\My%20Image1.png" Local Const $sMessage = "Directory to change excel image paths" Local $sFileSelectFolder = FileSelectFolder($sMessage, "") Local $sTempDir = @ScriptDir & "\testdir" ;Required 7-zip Local $PathZipProgram = @ProgramFilesDir & "\7-Zip\" If Not(FileExists($PathZipProgram & "\7z.exe")) Then MsgBox(16, "", "7z.exe not found in path " & $PathZipProgram) Exit EndIf ;look for excel files in selected directory and all subdirectories Local $SFileList = _FileListToArrayRec($sFileSelectFolder, "*.xls.;*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then For $i = 1 To $SFileList[0] DirRemove($sTempDir, 1) ;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file RunWait('"' & $PathZipProgram & '7z.exe" x -aoa -r "' & $SFileList[$i] & '" -o"' & $sTempDir & '" -y', $PathZipProgram, @SW_HIDE) __ReplaceImagePaths($sTempDir, $sFind, $sReplace) RunWait('"' & $PathZipProgram & '7z.exe" a -r "' & $SFileList[$i] & '" "' & $sTempDir & '\*" -tzip -y', $PathZipProgram, @SW_HIDE) Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf DirRemove($sTempDir, 1) Func __ReplaceImagePaths($sTempDir, $sFind, $sReplace) ;List all files with .xml.rels extension in the directory \xl\drawings\_rels Local $aFileList = _FileListToArray($sTempDir & "\xl\drawings\_rels", "*.xml.rels", 1, True) If @error = 1 Then ;MsgBox (0, "", "Path was invalid") SplashTextOn("Title", "Path was invalid", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf If @error = 4 Then ;MsgBox (0, "No files", "No files were found") SplashTextOn("Title", "No files were found", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf Local $iRetval ;Loop through the array For $i = 1 To $aFileList[0] $iRetval = _ReplaceStringInFile($aFileList[$i], $sFind, $sReplace) Next EndFunc  
      Some references:
      https://stackoverflow.com/questions/37145369/change-path-to-picture-links-in-excel http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp EDITED:
      Note: it seems that if User Account Control (UAC) is enabled then 7zip is unable to overwrite the destination file (using the same name).
      In this case, a possible solution would be to rename the original excel file before (see _PathSplit in help file).
      In my case i prefer just to disable UAC
    • SOF-TECH
      By SOF-TECH
      Dear all,
      Can someone show  me how to en hance the below function to write in CSV  into column  and rows the input values ? 
      I am getting this result: 

      I would like the result to be as this 

      From A1:C1 is for headers
      From A2:C2 is for input Data
      Global Const $GUI_EVENT_CLOSE = -3 $sDataFilePath = @ScriptDir & "\Records.csv" #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Demo1: New Record", 580, 115) $Input1 = GUICtrlCreateInput("", 10, 30, 270, 21) $Input2 = GUICtrlCreateInput("", 300, 30, 270, 21) $Input3 = GUICtrlCreateInput("", 10, 80, 270, 21) $Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17) $Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17) $Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17) $Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _ExportData() MsgBox(64, @ScriptName, "Record Saved.") EndSwitch WEnd Func _ExportData() If Not FileExists($sDataFilePath) Then FileWriteLine($sDataFilePath, "Name;ID;Phone No.;") EndIf For $i = $Input1 To $Input3 FileWrite($sDataFilePath, GUICtrlRead($i) & ";") Next FileWriteLine($sDataFilePath, "") EndFunc ;==>_ExportData May be Excel UDF has be to be added but I can manage that my self  
      Thank you in advance
×