ExcelChart UDF

[water]

You are right, it should be easy to create the line charts you need.

If you download the UDF there is an example script for every function. Please start with _XLChart_ChartCreate and you will see the power of the UDF.

[supersonic]

Hi water,

thank you both again for this UDF!

Currently I'm using it to prepare charts for our accounting and controlling department.

I wonder if a chart could be saved as a BMP or JPG...

[water]

Please check function _XLChart_ChartExport: Exports the chart in a graphic format (GIF, JPG, PNG ...) or as PDF/XPS.

[supersonic]

Oh, thank you, obviously I overlooked that function...

[valiryon]

When creating charts using _XLChart_ChartCreate(), the chart name is not being set (it seems to work fine for the name of a charts created on a chart sheet).

For example, if I pass "A" into the $sXLC_ChartName parameter, and run _XLChart_ChartsGet(), the chart name is listed as "Chart 1". It is also listed as "Chart 1" in the Layout ribbon with the chart selected in excel.

I've added a messagebox to the _XLChart_ChartCreate() to make sure the value is passed in properly, and it is. The $oXLC_Chart.Name value will display as "SheetName Chart 1" where sheet name is the name of the sheet that has the chart.

Is it possible to get more info on what is wrong? Or perhaps have _XLChart_ChartsGet() also pull the chart title?

This UDF is otherwise very handy so far! Your beta release was perfect timing!

[water]

What version of Excel do you use?

We have noticed a strange behaviour with the name property but haven't been able to find the reason.

[water]

Version 0.2.2.0 has been released.

Have fun testing!

For download please see my signature.

[GreenCan]

In post 1, you will now also find the ExcelChart Reference sheet for the ExcelChart UDF

The sheet is a cross reference list and will help you to find the functions that set or change a chart object.

The list is not complete and I would appreciate if you could help us completing it. If you have any comments or queries, don't hesitate to advise me or water.

GreenCan

Edited February 17, 2012 by GreenCan

[water]

GreenCan and I have released a quite complete version of the UDF a week ago.

There are nearly no questions and no like/dislike/please enhance postings on any of the related threads.

We assume that you are just a bit lazy to write some text and let us know what you think of this UDF.

So may we suggest that you either use the "Like This" button which can be found on the right lower side of the first post?

Or you click on the stars on the top of each page. Clicking on the right star rates the topic as excellent, clicking on the left star rates the UDF as poor.

So please give us some feedback so we know if we should put some more effort into this UDF (create a wiki, enhance the cross reference etc.).

[Saxo]

Hi,

this looks like really cool UDF stuff, but I'm having problems getting $xlStockOHLC (some other chart type is created) and _XLChart_ChartExport to work (.png file is created but does not contain the chart exported from Excel). What am I doing wrong?

Best regards,

Saxo

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Res_requestedExecutionLevel=asInvoker
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <ExcelChart.au3>
#include <Array.au3>
#include <Excel.au3>
Example()
Exit
Func Example()
Global $oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, "Date", 1, 1)
_ExcelWriteCell($oExcel, "02/24/2012", 2, 1)
_ExcelWriteCell($oExcel, "02/23/2012", 3, 1)
_ExcelWriteCell($oExcel, "02/22/2012", 4, 1)
_ExcelWriteCell($oExcel, "02/21/2012", 5, 1)
_ExcelWriteCell($oExcel, "02/17/2012", 6, 1)
$oExcel.ActiveSheet.Columns(1).AutoFit
_ExcelWriteCell($oExcel, "Open", 1, 2)
_ExcelWriteCell($oExcel, "68.682", 2, 2)
_ExcelWriteCell($oExcel, "69.342", 3, 2)
_ExcelWriteCell($oExcel, "69.203", 4, 2)
_ExcelWriteCell($oExcel, "69.204", 5, 2)
_ExcelWriteCell($oExcel, "69.485", 6, 2)
$oExcel.ActiveSheet.Columns(2).AutoFit
_ExcelWriteCell($oExcel, "High", 1, 3)
_ExcelWriteCell($oExcel, "68.826", 2, 3)
_ExcelWriteCell($oExcel, "69.375", 3, 3)
_ExcelWriteCell($oExcel, "69.463", 4, 3)
_ExcelWriteCell($oExcel, "69.364", 5, 3)
_ExcelWriteCell($oExcel, "69.598", 6, 3)
$oExcel.ActiveSheet.Columns(3).AutoFit
_ExcelWriteCell($oExcel, "Low", 1, 4)
_ExcelWriteCell($oExcel, "68.263", 2, 4)
_ExcelWriteCell($oExcel, "68.654", 3, 4)
_ExcelWriteCell($oExcel, "69.085", 4, 4)
_ExcelWriteCell($oExcel, "68.891", 5, 4)
_ExcelWriteCell($oExcel, "69.251", 6, 4)
$oExcel.ActiveSheet.Columns(4).AutoFit
_ExcelWriteCell($oExcel, "Close", 1, 5)
_ExcelWriteCell($oExcel, "68.403", 2, 5)
_ExcelWriteCell($oExcel, "68.882", 3, 5)
_ExcelWriteCell($oExcel, "69.307", 4, 5)
_ExcelWriteCell($oExcel, "68.135", 5, 5)
_ExcelWriteCell($oExcel, "69.450", 6, 5)
$oExcel.ActiveSheet.Columns(5).AutoFit
$Graph_position = "K2:T24"
$XValueRange = "=Sheet1!R2C1:R6C1"
Dim $DataRange[3]=[2,"=Sheet1!R2C2:R6C2","=Sheet1!R2C5:R6C5"]
Dim $DataName [3]=[2,"=Sheet1!R1C2:R1C2","=Sheet1!R1C5:R1C5"]
Local $iWorksheet = 1
Local $oChart1 = _XLChart_ChartCreate($oExcel, $iWorksheet, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName);, True, "Sales", "Date", "", "Quantity")
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
Local $iFile = @ScriptDir &"TestExcelChartExport.png"
_XLChart_ChartExport($oChart1, $iFile, "")
Return
EndFunc ;==> Example

Edited February 26, 2012 by Saxo

[water]

I will answer on the

This thread is to discuss functionality or needed enhancements of the UDF.

[water]

Version 0.3.0.0 of the UDF has been released.

Have fun testing!

For download please see my signature.

[water]

More than 400 downloads and no questions?

Do you guys only play a bit with the UDF or do you use it in production?

[nicdev007]

More than 400 downloads and no questions?

Do you guys only play a bit with the UDF or do you use it in production?

Hi water and GreenCan,

Just a note to thank you for this great UDF. I was collecting data in my script from a piece of hardware and already storing it in an excel book. I thought it would be a nice touch to have the graph "automagically" drawn just before the file is closed. I thought it would be quite tricky, but then I found your great UDF, and really it works like a charm! Easy to use and understand through the examples. I managed to adapt my script in a very short time to use your UDF.

Keep up the great work! Your UDF is pure wizardry

NicdevBest

[water]

Hi nicdev007,

thanks for your kind words!

Glad to see the UDF is useful for someone. Compared with my Active Directory and Outlook UDF the questions come in really slow. Either nobody uses the UDF or it is so simple to use that there don't arise any questions.

Thanks for telling us that it is the latter.

[water]

Version 0.3.1.1 of the UDF has been released.

Have fun testing!

For download please see my signature.

Edited January 21, 2013 by water

[water]

Excelent! (like mr Burns)

Right now im doing alot of charts in excel and im hoping that someone could come up with some simple UDFs for simple chart manipulation. I checked your excelchart UDF Water, but it seems to complicated for the things that i would like to do. I would like to just add some data to the laste cell (witch i do today with the standard excel.au3) and then increase the "range" of an existing chart to include the new values. Then export the chart to a PNG file (or clipboard). Is there a simple way of doing this without involving macros and stuff?

 

I appritiate the help. Please let me know if i should move this discussion to another tread...

I dont want to create a new chart. I want to modify the existing one.

I have around 20 excel documents with charts that i regulary update with new data. So would i would like to do is:

Open the existing excel, add the new data (a new name and a new value) <- Ive completed this part. Then i need to make sure that the existing chart include my newly added values. Then i need to sort by the values column. Then export the chart as a PNG and then save the document again. Sometimes a also may need to increase the size of the chart.

 

At the moment there is not function to update the datarange of a chart. But this should work:

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

This sets the Xrange and the datarange for the first data series.

[Tjalve]

At the moment there is not function to update the datarange of a chart. But this should work:

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

This sets the Xrange and the datarange for the first data series.

GREAT stuff! I will try it as soon as i get the chanse. Is there a simpel way to sort the data in a column aswell? This is a performance chart and i nheed the festest on the top

[water]

Sorting Excel data is a pure Excel thing. So you need to have a look at the Excel COM and do it yourself or use my - Alpha version - of the rewrite of the Excel UDF - function _Excel_RangeSort.

[Tjalve]

Thansk to your Excel rewrite UD i got sorting working. However i still cant get my head around your code here above, Any chance you could provide me with some saple code?