Jump to content

ExcelChart UDF


water
 Share

Recommended Posts

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 weeks later...

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Version 0.2.2.0 has been released.

Have fun testing!

For download please see my signature.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by GreenCan

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Link to comment
Share on other sites

  • 2 weeks later...

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

#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 by Saxo
Link to comment
Share on other sites

I will answer on the

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 months later...

Version 0.3.0.0 of the UDF has been released.

Have fun testing!

For download please see my signature.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 5 months later...

More than 400 downloads and no questions?

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 1 month later...

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 :sorcerer:

NicdevBest

Life is too short to worry about the things you don't have or cannot do ... So if you don't know how to do it - Learn it! Don't be afraid to ask for help ...

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 1 month later...

Version 0.3.1.1 of the UDF has been released.

Have fun testing!

For download please see my signature.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 5 months later...

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 3 months later...

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
 Share

×
×
  • Create New...