Jump to content

ExcelChart UDF - Help & Support


Recommended Posts

==Originally posted on the download thread (can't edit/remove it that I can see) ==

When creating charts using _XLChart_ChartCreate(), the chart name is not being set (it seems to work for 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 post
Share on other sites
  • Replies 134
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

This works for me and lets you save the combined charts as a single JPG: $oExcel.ActiveSheet.Chartobjects.CopyPicture() ; Copy all chart objects of the active sheet to the clipboard as a single pict

The UDF does not support log scales. But if you have a look at the ScaleType property (https://docs.microsoft.com/en-za/office/vba/api/excel.axis.scaletype) and the LogBase property (https://docs.mi

Modified the Readme.txt. What do you think?  

Posted Images

To edit/remove a post you have to have at least a post count of 5 - IIRC.

I'm going to answer your question here.

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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

I confirm that this is an issue on my Excel 2007 as well as Excel 2010.

I didn't notice the issue because I didn't focus on the chart names (yet...)... sorry for this.

In debug mode I get effectively following error:

Error 2147352567 (80020009) 'Not enough storage is available to complete this operation'

for this line:

If $sXLC_ChartName <> "" Then $oXLC_Chart.Name = $sXLC_ChartName

When I use the raw code outside the function, for example

$oChart.Name = "Main Chart"

I get the same error.

I will search for a workaround and if possible for a bugfix.

Changing the chart name in interactive mode (Layout, Properties) works, so I guess we should find what is wrong in the code.

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 post
Share on other sites

I think this remark explains what's going on.

"This property is read-only for chart objects (embedded charts)."

So you can set the name for a chartsheet but not for a chart object.

In the next version of the UDF the parameter will be ignored for embedded charts work for both chartsheets and embedded charts.

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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

You are correct water but there is a solution...

This works:

$oChart1.Parent.Name = "Name of this Chart"

@valiryon, you can use this as a workaround until we come with a new release. that will be pretty soon (a matter of days).

... I am having a coffee break now :)

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 post
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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • 2 weeks later...

@Saxo

Can you please post a screenshot of the chart you get and - if possible - a screenshot of the chart as it should look like?

( to the original post).

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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Water, thank you for your very fast reply. I'm not sure how to post the image, but it is a normal 2D column chart with dates on the x-axis and price on the y-axis. 2 different columns are shown per date. Hope this helps you in your investigation.

Link to post
Share on other sites

IIRC you will need to have at least 5 posts before you can add attachments to your posts.

I will test your script tomorrow when I'm in the office and post the results.

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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Hi Saxo,

some small changes and your chart should work:

In the UDF please change function _XLChart_ChartCreate. Move line

$oXLC_Chart.ChartType = $iXLC_ChartType
to the end of the function before lines
If $bXLC_ScreenUpdate = False Then $oXLC_Excel.ScreenUpdating = $iXLC_ScreenUpdating
Return SetError(0, $iXLC_ErrorHandler, $oXLC_Chart)

EndFunc   ;==>_XLChart_ChartCreate

Then please modify your script like this:

#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()
    Local $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"
    Local $DataRange[5] = [4, "=Sheet1!R2C2:R6C2", "=Sheet1!R2C3:R6C3", "=Sheet1!R2C4:R6C4","=Sheet1!R2C5:R6C5"]
    Local $DataName[5] = [4, "=Sheet1!B1", "=Sheet1!C1", "=Sheet1!D1", "=Sheet1!E1"]
    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, "PNG", False, True)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    Return
EndFunc   ;==>Example
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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Thx water - works great! I have continued with the example. My goal is to calculate Pivot Points (see http://en.wikipedia.org/wiki/Pivot_point) and plot them in the Excel chart per date bar. Is it possible to make such plots and if so, how is it done (preferably with with different coloring and labeling of the 5 points per bar)?

Calculating the Pivot Points inside Excel has faced me with the following issues:

  • I would like to format the prices so that they are written e.g. 69,450 instead of 69.450 (I assume that .NumberFormat should be used somehow). Currently the calculations are messed up because of this.
  • Is there a smarter way for me to write the same formula accros many rows (other than repeating it in the code like I did)?
Below I have pasted the code like it looks now.

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()
    Local $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, "O", 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, "H", 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, "L", 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, "C", 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
    ;$oExcel.ActiveSheet.Range("B2").NumberFormat = "#,###" ; How to change e.g. "69.450" to "69,450" ... for all price cells?
    _ExcelWriteCell($oExcel, "P=(O+H+L+C)/4", 1, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R2C2:R2C5)/4", 2, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R3C2:R3C5)/4", 3, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R4C2:R4C5)/4", 4, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R5C2:R5C5)/4", 5, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R6C2:R6C5)/4", 6, 6)
    $oExcel.ActiveSheet.Columns(6).AutoFit
    _ExcelWriteCell($oExcel, "S2=P-(H-L) ", 1, 7)
    _ExcelWriteFormula($oExcel, "=R2C6-(R2C3-R2C4)", 2, 7)
    _ExcelWriteFormula($oExcel, "=R3C6-(R3C3-R3C4)", 3, 7)
    _ExcelWriteFormula($oExcel, "=R4C6-(R4C3-R4C4)", 4, 7)
    _ExcelWriteFormula($oExcel, "=R5C6-(R5C3-R5C4)", 5, 7)
    _ExcelWriteFormula($oExcel, "=R6C6-(R6C3-R6C4)", 6, 7)
$oExcel.ActiveSheet.Columns(7).AutoFit
_ExcelWriteCell($oExcel, "S1=2×P-H", 1, 8)
    _ExcelWriteFormula($oExcel, "=2*R2C6-R2C3", 2, 8)
    _ExcelWriteFormula($oExcel, "=2*R3C6-R3C3", 3, 8)
    _ExcelWriteFormula($oExcel, "=2*R4C6-R4C3", 4, 8)
    _ExcelWriteFormula($oExcel, "=2*R5C6-R5C3", 5, 8)
    _ExcelWriteFormula($oExcel, "=2*R6C6-R6C3", 6, 8)
$oExcel.ActiveSheet.Columns(8).AutoFit
_ExcelWriteCell($oExcel, "R1=2×P-L", 1, 9)
    _ExcelWriteFormula($oExcel, "=2*R2C6-R2C4", 2, 9)
    _ExcelWriteFormula($oExcel, "=2*R3C6-R3C4", 3, 9)
    _ExcelWriteFormula($oExcel, "=2*R4C6-R4C4", 4, 9)
    _ExcelWriteFormula($oExcel, "=2*R5C6-R5C4", 5, 9)
    _ExcelWriteFormula($oExcel, "=2*R6C6-R6C4", 6, 9)
    $oExcel.ActiveSheet.Columns(9).AutoFit
    _ExcelWriteCell($oExcel, "R2=P+(H-L)", 1, 10)
    _ExcelWriteFormula($oExcel, "=R2C6+(R2C3-R2C4)", 2, 10)
    _ExcelWriteFormula($oExcel, "=R3C6+(R3C3-R3C4)", 3, 10)
    _ExcelWriteFormula($oExcel, "=R4C6+(R4C3-R4C4)", 4, 10)
    _ExcelWriteFormula($oExcel, "=R5C6+(R5C3-R5C4)", 5, 10)
    _ExcelWriteFormula($oExcel, "=R6C6+(R6C3-R6C4)", 6, 10)
    $oExcel.ActiveSheet.Columns(10).AutoFit
$Graph_position = "K2:T24"
    $XValueRange = "=Sheet1!R2C1:R6C1"
    Local $DataRange[5] = [4, "=Sheet1!R2C2:R6C2", "=Sheet1!R2C3:R6C3", "=Sheet1!R2C4:R6C4","=Sheet1!R2C5:R6C5"]
    Local $DataName[5] = [4, "=Sheet1!B1", "=Sheet1!C1", "=Sheet1!D1", "=Sheet1!E1"]
    Local $iWorksheet = 1
    Local $oChart1 = _XLChart_ChartCreate($oExcel, $iWorksheet, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    Local $iFile = @ScriptDir & "TestExcelChartExport.png"
    _XLChart_ChartExport($oChart1, $iFile, "PNG", False, True)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    Return
EndFunc   ;==>Example
Link to post
Share on other sites

@water,

Good fix!

@saxo,

Nice example.

I advise you to put the following line in the beginning of the script:

Global $iXLC_Debug = 2 (or = 1)

This will help you finding issues in your scipt, it helped water finding a bug :oops: in _XLChart_ChartCreate.

Thanks for your interest

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 post
Share on other sites

Hi,

I have not been able to figure out how to add a new series to the chart created. After the _XLChart_ChartCreate I have tried with:

;_XLChart_SeriesSet($oExcel.SeriesCollection(6), $xlLine)

;_XLChart_SeriesSet($oExcel[1].SeriesCollection(6), $xlLine)

;$oNewChartSerie = $oChart1.SeriesCollection.NewSeries

;$oNewChartSerie = $oExcel.SeriesCollection(6)

;_XLChart_SeriesSet($oNewChartSerie, $xlLine)

My aim is to plot a line per calculate point (S2, S1, P, R1, R2) per date, so that it on a date e.g. the second would look something like:

_

_

_

_

_

2

I believe that I with 5 points per day for 5 days would need 25 series where each serie would only contain a value for the date it needs to be calculated for (the rest of the dates would have blank value). I don't know if this is the right way or if there is a smarter way. But I'm stuck as long as I don't know how to add a line to the existing chart.

_

Edited by Saxo
Link to post
Share on other sites

Use function _XLChart_SeriesAdd to add a data series to a chart. After the function call please check the returncode, @error and @extended.

To get additional COM error information please set

$iXLChart_Debug = 2
at the top of your script (after #include <ExcelChart.au3> of course).

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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Hi Saxo,

to add an additional data series add the following code at the end of your script:

; Add a data series
_XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C8:R6C8", "=Sheet1!H1")
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
; Set the type of the data series
_XLChart_SeriesSet($oChart1.SeriesCollection(5), $XlLine)
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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

To set colors on the up and down bars and to make the bars smaller (by increasing the gap between the bars) use the following code:

; Format chart
_XLChart_FillSet($oChart1.Chartgroups(1).UpBars, -4)    ; Set the UpBars to green
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_FillSet($oChart1.Chartgroups(1).DownBars, -3)  ; Set the DownBars to red
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
_XLChart_ColumnGroupSet($oChart1.ChartGroups(1), 450)   ; Make the bars smaller (by making the gap between the bars bigger)
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Thanks water - this managed to move me forward.

  • I have now tried to plot in the R2 values per date. For this I can't use $XlLine, because if I used this and only plot one value, then nothing is shown. So as an alternative I have used $xlLineMarkers - but I would prefer that a line (just as wide as the bar) is shown. Is this possible?
  • The current $xlLineMarkers solution shows some strange output, because D1, D3 & D4 shows R2 below H (?). R2 should be well above H. Is this an error?
  • If point 1) can be solved, then I would like R2 on each date to look the same in terms of color and style (currently Excel picks a new color and marker style per new line that I add). How can this be done?
  • Is it possible to call Excel in hidden mode?
The code currently looks like this:

#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 $iXLC_Debug = 2 ;(or = 1)
    Local $oExcel = _ExcelBookNew()
    _ExcelWriteCell($oExcel, "Date", 1, 1)
    _ExcelWriteCell($oExcel, "D1", 2, 1)
_ExcelWriteCell($oExcel, "D2", 3, 1)
    _ExcelWriteCell($oExcel, "D3", 4, 1)
    _ExcelWriteCell($oExcel, "D4", 5, 1)
    _ExcelWriteCell($oExcel, "D5", 6, 1)
    $oExcel.ActiveSheet.Columns(1).AutoFit
    _ExcelWriteCell($oExcel, "O", 1, 2)
    _ExcelWriteCell($oExcel, "69,342", 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, "H", 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, "L", 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, "C", 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
    _ExcelWriteCell($oExcel, "P=(O+H+L+C)/4", 1, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R2C2:R2C5)/4", 2, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R3C2:R3C5)/4", 3, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R4C2:R4C5)/4", 4, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R5C2:R5C5)/4", 5, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R6C2:R6C5)/4", 6, 6)
    $oExcel.ActiveSheet.Columns(6).AutoFit
    _ExcelWriteCell($oExcel, "S2=P-(H-L) ", 1, 7)
    _ExcelWriteFormula($oExcel, "=R2C6-(R2C3-R2C4)", 2, 7)
    _ExcelWriteFormula($oExcel, "=R3C6-(R3C3-R3C4)", 3, 7)
    _ExcelWriteFormula($oExcel, "=R4C6-(R4C3-R4C4)", 4, 7)
    _ExcelWriteFormula($oExcel, "=R5C6-(R5C3-R5C4)", 5, 7)
    _ExcelWriteFormula($oExcel, "=R6C6-(R6C3-R6C4)", 6, 7)
$oExcel.ActiveSheet.Columns(7).AutoFit
_ExcelWriteCell($oExcel, "S1=2×P-H", 1, 8)
    _ExcelWriteFormula($oExcel, "=2*R2C6-R2C3", 2, 8)
    _ExcelWriteFormula($oExcel, "=2*R3C6-R3C3", 3, 8)
    _ExcelWriteFormula($oExcel, "=2*R4C6-R4C3", 4, 8)
    _ExcelWriteFormula($oExcel, "=2*R5C6-R5C3", 5, 8)
    _ExcelWriteFormula($oExcel, "=2*R6C6-R6C3", 6, 8)
$oExcel.ActiveSheet.Columns(8).AutoFit
_ExcelWriteCell($oExcel, "R1=2×P-L", 1, 9)
    _ExcelWriteFormula($oExcel, "=2*R2C6-R2C4", 2, 9)
    _ExcelWriteFormula($oExcel, "=2*R3C6-R3C4", 3, 9)
    _ExcelWriteFormula($oExcel, "=2*R4C6-R4C4", 4, 9)
    _ExcelWriteFormula($oExcel, "=2*R5C6-R5C4", 5, 9)
    _ExcelWriteFormula($oExcel, "=2*R6C6-R6C4", 6, 9)
    $oExcel.ActiveSheet.Columns(9).AutoFit
    _ExcelWriteCell($oExcel, "R2=P+(H-L)", 1, 10)
    _ExcelWriteFormula($oExcel, "=R2C6+(R2C3-R2C4)", 2, 10)
    _ExcelWriteFormula($oExcel, "=R3C6+(R3C3-R3C4)", 3, 10)
    _ExcelWriteFormula($oExcel, "=R4C6+(R4C3-R4C4)", 4, 10)
    _ExcelWriteFormula($oExcel, "=R5C6+(R5C3-R5C4)", 5, 10)
    _ExcelWriteFormula($oExcel, "=R6C6+(R6C3-R6C4)", 6, 10)
    $oExcel.ActiveSheet.Columns(10).AutoFit
_ExcelWriteCell($oExcel, "R2 on D1", 1, 11)
    _ExcelWriteFormula($oExcel, "=R2C6+(R2C3-R2C4)", 2, 11)
    _ExcelWriteFormula($oExcel, "", 3, 11)
    _ExcelWriteFormula($oExcel, "", 4, 11)
    _ExcelWriteFormula($oExcel, "", 5, 11)
    _ExcelWriteFormula($oExcel, "", 6, 11)
    $oExcel.ActiveSheet.Columns(11).AutoFit
_ExcelWriteCell($oExcel, "R2 on D2", 1, 12)
    _ExcelWriteFormula($oExcel, "", 2, 12)
    _ExcelWriteFormula($oExcel, "=R3C6+(R3C3-R3C4)", 3, 12)
    _ExcelWriteFormula($oExcel, "", 4, 12)
    _ExcelWriteFormula($oExcel, "", 5, 12)
    _ExcelWriteFormula($oExcel, "", 6, 12)
    $oExcel.ActiveSheet.Columns(12).AutoFit
_ExcelWriteCell($oExcel, "R2 on D3", 1, 13)
    _ExcelWriteFormula($oExcel, "", 2, 13)
    _ExcelWriteFormula($oExcel, "", 3, 13)
    _ExcelWriteFormula($oExcel, "=R4C6+(R4C3-R4C4)", 4, 13)
    _ExcelWriteFormula($oExcel, "", 5, 13)
    _ExcelWriteFormula($oExcel, "", 6, 13)
    $oExcel.ActiveSheet.Columns(13).AutoFit
_ExcelWriteCell($oExcel, "R2 on D4", 1, 14)
    _ExcelWriteFormula($oExcel, "", 2, 14)
    _ExcelWriteFormula($oExcel, "", 3, 14)
    _ExcelWriteFormula($oExcel, "", 4, 14)
    _ExcelWriteFormula($oExcel, "=R5C6+(R5C3-R5C4)", 5, 14)
    _ExcelWriteFormula($oExcel, "", 6, 14)
    $oExcel.ActiveSheet.Columns(14).AutoFit
_ExcelWriteCell($oExcel, "R2 on D5", 1, 15)
    _ExcelWriteFormula($oExcel, "", 2, 15)
    _ExcelWriteFormula($oExcel, "", 3, 15)
    _ExcelWriteFormula($oExcel, "", 4, 15)
    _ExcelWriteFormula($oExcel, "", 5, 15)
    _ExcelWriteFormula($oExcel, "=R6C6+(R6C3-R6C4)", 6, 15)
    $oExcel.ActiveSheet.Columns(15).AutoFit
$Graph_position = "B8:K32"
    $XValueRange = "=Sheet1!R2C1:R6C1"
    Local $DataRange[5] = [4, "=Sheet1!R2C2:R6C2", "=Sheet1!R2C3:R6C3", "=Sheet1!R2C4:R6C4","=Sheet1!R2C5:R6C5"]
    Local $DataName[5] = [4, "=Sheet1!B1", "=Sheet1!C1", "=Sheet1!D1", "=Sheet1!E1"]
    Local $iWorksheet = 1
    Local $oChart1 = _XLChart_ChartCreate($oExcel, $iWorksheet, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName)
; Add a data series
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C11:R6C11", "=Sheet1!K1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
_XLChart_SeriesSet($oChart1.SeriesCollection(5), $xlLineMarkers)
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C12:R6C12", "=Sheet1!L1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
_XLChart_SeriesSet($oChart1.SeriesCollection(6), $xlLineMarkers)
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C13:R6C13", "=Sheet1!M1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
_XLChart_SeriesSet($oChart1.SeriesCollection(7), $xlLineMarkers)
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C14:R6C14", "=Sheet1!N1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
_XLChart_SeriesSet($oChart1.SeriesCollection(8), $xlLineMarkers)
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C15:R6C15", "=Sheet1!O1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
_XLChart_SeriesSet($oChart1.SeriesCollection(9), $xlLineMarkers)
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
; Set the type of the data series
    ;_XLChart_SeriesSet($oChart1.SeriesCollection(5), $XlLine)
    ;_XLChart_SeriesSet($oChart1.SeriesCollection(5), $xlLineMarkers)  ; HOW TO make a '_' instead of a '*' marking?
    ;_XLChart_FillSet($oChart1.SeriesCollection(5).Line, -4)          ; HOW TO color the line and pick it's style?
;_XLChart_FillSet($oExcel[1].SeriesCollection(5).Line, -4)
;$oExcel[1].SeriesCollection(11).Points(5)
; Format chart
    _XLChart_FillSet($oChart1.Chartgroups(1).UpBars, -4)    ; Set the UpBars to green
    If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
    _XLChart_FillSet($oChart1.Chartgroups(1).DownBars, -3)  ; Set the DownBars to red
    If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
    _XLChart_ColumnGroupSet($oChart1.ChartGroups(1), 450)   ; Make the bars smaller (by making the gap between the bars bigger)
    If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate' on line " & @ScriptLineNumber)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    Local $iFile = @ScriptDir & "TestExcelChartExport.png"
    _XLChart_ChartExport($oChart1, $iFile, "PNG", False, True)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    Return
EndFunc   ;==>Example
Link to post
Share on other sites

4) To enhance performance and stop flickering you can disable screen updating by adding this at the top of your function

; Disable Screen updating (forced even when debugging is activated)
_XLChart_ScreenUpdateSet($oExcel, 2)
and this at the end of the function
; Enable Screen updating
_XLChart_ScreenUpdateSet($oExcel, 1)

The other "problems" are under investigation.

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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

1) The additonal lines you want to plot should look like the chart you linked to on Wikipedia? A horizontal line with "R1" etc. on both ends?

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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
    • By Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
    • By Hermes
      Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows.
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <File.au3> #include <IE.au3> #include <Array.au3> #include <INet.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> #Include "WinHttp.au3" #Include "_HtmlTable2Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "table1.html") _WD_LoadWait($sSession) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") ;ConsoleWrite ("mat-table " & $sElement & @CRLF) Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True) sleep(1000) For $i = 0 to UBound($aArray1) - 1 $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text') Next ;_ArrayDisplay($aArray1) ;Email variables $SmtpServer = "" ; address for the smtp-server to use - REQUIRED $FromName = "Hermes" ; name from who the email was sent $FromAddress = "sender@gmail.com" ; address from where the mail should come $ToAddress = "recipient@gmail.com" ; destination address of the email - REQUIRED, use commas (,) to add more email addresses $Subject = "File not found" ; subject from the email - can be anything you want it to be $Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail $AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed $CcAddress = "" ; address for cc - leave blank if not needed $BccAddress = "" ; address for bcc - leave blank if not needed $Importance = "High" ; Send message priority: "High", "Normal", "Low" $Username = "" ; username for the account used from where the mail gets sent - REQUIRED $Password = "" ; password for the account used from where the mail gets sent - REQUIRED $IPPort = 25 ; port used for sending the mail $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS $tls = 0 ; enables/disables TLS when required Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters If FileExists($sWorkbook) Then ;Check if the file exist. Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $iIdx Local $Skipline = 0 ;0==> first line Do Local $temprf For $i = 0 To UBound($aArray2) - 1 $temprf &= $aArray2[$i] _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]") Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True) $iIdx = _ArraySearch($aArray1, $aArray2[$i]) If @error Then ContinueLoop _WD_ElementAction($sSession, $aElement[$iIdx], 'click') If $i < $Skipline Then ContinueLoop $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1) _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) ;Paste Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton") _WD_ElementAction($sSession, $oTest4, 'click') Sleep(1000) ;Save Button Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button") _WD_ElementAction($sSession, $save3, 'click') _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1) sleep(1000) Next Until (Not @error) _Excel_Close($oWorkbook) Else _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) Exit EndIf _WD_LoadWait($sSession) ;Attaching files to emails Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 Local $i_Error_desciption = "" If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress $objEmail.Subject = $s_Subject If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then $objEmail.HTMLBody = $as_Body Else $objEmail.Textbody = $as_Body & @CRLF EndIf $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer If Number($IPPort) = 0 then $IPPort = 25 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort ;Authenticated SMTP If $s_Username <> "" Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password EndIf ; Set security params If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True ;Update settings $objEmail.Configuration.Fields.Update ; Set Email Importance Switch $s_Importance Case "High" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High" Case "Normal" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal" Case "Low" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low" EndSwitch $objEmail.Fields.Update ; Sent the Message $objEmail.Send $objEmail="" EndFunc ;==>_INetSmtpMailCom Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) Sleep(2000) For $i = 1 To $aDir[0] DirRemove($aDir[$i], $DIR_REMOVE) Next _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome If the excel file doesn't exists in the folder, it will send an email to a specific recipient.
      What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row
      Appreciate any help that I can get to achieve this.
      table1.html test.xlsx
×
×
  • Create New...