ExcelChart UDF

[water]

Sure!

Every function of the UDF comes with an example script (check the content of the ZIP file).

Which function do you have problems with?

[Tjalve]

Sure!

Every function of the UDF comes with an example script (check the content of the ZIP file).

Which function do you have problems with?

The samll code you gave me befor

"

$oChart.SeriesCollection(1).XValues = $sXValueRange ; X values
$oChart.SeriesCollection(1).Values = $vDataRange

"

[water]

I see. The sample I provided in July

Did you try and do you get any errors?

[Tjalve]

I see. The sample I provided in July

Did you try and do you get any errors?

Im actually not sure how to use it.

Im guessing that i need to specify the chart in question with an object varible. But im not sure on how to do that.

I guess that this is the: $oChart variable. But i dont know how to declare that variable.

Im thinking like this.

Open an Excel object

Open the chart object

Do the modifications

close the chart object

close the excel objekt

right?

[water]

I will have a look and come up with an example.

[Tjalve]

I will have a look and come up with an example.

I apopritiate it. Thanx

[water]

This example creates a chart with 3 out of 5 values. When you click OK values 4 and 5 are included in the chart:

#include-once
#include <ExcelChart.au3>

; *****************************************************************************
; Create example environment
; *****************************************************************************
Global $aExcel = _XLChart_Example()
If @error = 2 Then Exit MsgBox(16, "Excel Chart Example Script", "The installed Excel version is not supported by this UDF!" & @CRLF & "Version must be >= 12 (Excel 2007).")
If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _XLChart_Example on line " & @ScriptLineNumber)

; *****************************************************************************
; 3D COLUMN
; *****************************************************************************
Global $Graph_position = "=_XLChart_Example!A8:F26"
Global $XValueRange = "=_XLChart_Example!A2:A4"
Global $DataRange[3] = [2, "=_XLChart_Example!B2:B4", "=_XLChart_Example!C2:C4"]
Global $DataName[3] = [2, "=_XLChart_Example!B1", "=_XLChart_Example!C1"]
Global $oChart = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DColumn, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date", "Location", "Quantity")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)

; Set new values
MsgBox(0, "", "Updating the chart")
$XValueRange = "=_XLChart_Example!A2:A6"
$DataRange[1] = "=_XLChart_Example!B2:B6"
$DataRange[2] = "=_XLChart_Example!C2:C6"
_XLChart_ChartDataSet($oChart, $XValueRange, $DataRange, $DataName)

Edited October 20, 2013 by water

[Tjalve]

You are amazing!! Thanx man!

[water]

Glad to be of service

[lsakizada]

Hi,

I have a column that contain dates values such as "2/27/2015  10:41:53 AM".

Now when I set the X axis values (like in the example script:

"Local $XValueRange = "=_XLChart_Example_!R2C8:R" & $Str & "C8""

)

with this columns then the display is not set properly and the chart looks bad.

In the function "_XLChart_ChartCreate()" I see the following code but I do not know what to change so the X axis will display the dates as in the column.

$oChart.SeriesCollection($iIndex).Name = $vDataName[$iIndex] ; set name of values
$oChart.SeriesCollection($iIndex).XValues = $sXValueRange ; X values
$oChart.SeriesCollection($iIndex).Values = $vDataRange[$iIndex]

Edited March 4, 2015 by lsakizada

[water]

I'm on a business trip right now but will have a look as soon as possible.

[lsakizada]

I'm on a business trip right now but will have a look as soon as possible.

 

Thanks water,

Anyway, I just look at the example code with the dates and I found that you put single quote before the inserted date x-value, hence I did it in my code and its worked.

But I would appreciate if you will have a clean way to do that.

Regards and have a good trip one.

[jcpetu]

Hi Water, is it possible to show the graphs in a window without showing the Excel frame?

[water]

Thanks water,

Anyway, I just look at the example code with the dates and I found that you put single quote before the inserted date x-value, hence I did it in my code and its worked.

But I would appreciate if you will have a clean way to do that.

Regards and have a good trip one.

Dates are stored as numbers by Excel.The quote tells Excel to treat the date as string. So this is as clean as possible I think.

[water]

Hi Water, is it possible to show the graphs in a window without showing the Excel frame?

Are you talking about the graphs in an Excel sheet or in a chart sheet (please check example 5 in _XLChart_ChartCreate.au3)

[jcpetu]

On Autoit version 3.3.12.0, when I tried to run the example I got:

"C:\Program Files (x86)\AutoIt3\Include\ExcelConstants.au3"(195,58) : error: $xlTypeXPS previously declared as a 'Const'.
Global Const $xlTypeXPS = 1 ; "XPS" — XPS Document (.xps)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(227,60) : warning: $xlValue: possibly used before declaration.
 If $iDisplayUnit <> Default And $oObject.Type <> $xlValue Then
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(441,27) : warning: $xlCategory: possibly used before declaration.
  $oChart.Axes($xlCategory)
  ~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(667,50) : warning: $xlLocationAsObject: possibly used before declaration.
   $oChart = $oChart.Location($xlLocationAsObject,
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(2330,71) : warning: $xl3DColumn: possibly used before declaration.
     $aoCharts[$iIndex] = _XLChart_ChartCreate($oExcel, 1, $xl3DColumn,
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphics_XLChart_ChartCreate.au3"(86,67) : warning: $xlStockOHLC: possibly used before declaration.
Global $oChart1 = _XLChart_ChartCreate($aExcel[0], 1, $xlStockOHLC,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(227,60) : error: $xlValue: undeclared global variable.
 If $iDisplayUnit <> Default And $oObject.Type <> $xlValue Then
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(441,27) : error: $xlCategory: undeclared global variable.
  $oChart.Axes($xlCategory)
  ~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(667,50) : error: $xlLocationAsObject: undeclared global variable.
   $oChart = $oChart.Location($xlLocationAsObject,
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(2330,71) : error: $xl3DColumn: undeclared global variable.
     $aoCharts[$iIndex] = _XLChart_ChartCreate($oExcel, 1, $xl3DColumn,
     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphics_XLChart_ChartCreate.au3"(86,67) : error: $xlStockOHLC: undeclared global variable.
Global $oChart1 = _XLChart_ChartCreate($aExcel[0], 1, $xlStockOHLC,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphicsExcelChart.au3"(665,40) : error: _ExcelSheetActivate(): undefined function.
   _ExcelSheetActivate($oExcel, $vSheet)
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphics_XLChart_ChartCreate.au3"(47,43) : error: _ExcelWriteCell(): undefined function.
_ExcelWriteCell($aExcel[0], "Date", 28, 14)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:TempA30000-ADScanGraphics_XLChart_ChartCreate.au3"(104,48) : error: _ExcelReadCell(): undefined function.
Global $Title = _ExcelReadCell($aExcel[0], "B1")
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:TempA30000-ADScanGraphics_XLChart_ChartCreate.au3 - 10 error(s), 5 warning(s)

[water]

The examples need to be run with AutoIt 3.3.8.1.

As it has been so quiet here I was not sure the UDF is being used by anyone.

As soon as I find some spare time I will update the UDF and the example scripts to work with the newest production version.

[jcpetu]

Thanks a lot Water.

Regards.

[lsakizada]

As it has been so quiet here I was not sure the UDF is being used by anyone.

As soon as I find some spare time I will update the UDF and the example scripts to work with the newest production version.

 

@Water, thanks for sharing it! I am using it for creating profiling reports where I work.

I already created several tools that reports excel charts with this UDF.

Thanks alot!

[water]

Version 0.4.0.0 of the UDF has been released. Only changes made are to make it run with Autoit 3.3.12.0!

Have fun testing!

For download please see my signature.

Edited March 13, 2015 by water