Jump to content
water

ExcelChart UDF - Help & Support

Recommended Posts

1) A label for e.g. R1 at one end of the line would be enough for me. If the other end could carry the value (like it does in the second chart in the Wiki-link), then that would be great.

4) Thanks for the hint.

.. and thank you for helping me out.

Best regards,

Saxo

Share this post


Link to post
Share on other sites

Hi Saxo,

I've been playing with your chart and was able to create something like this by removing the line and setting the markers size, type and data labels.

But I fear it's not exactly what you are looking for.

Could you please post a screenshot what your chart should look like? If it's confidential you can send me a PM and I'm going to delete it when no longer needed.

Have you already created such a chart by hand?

If yes, you could record a macro if you are running Excel 2010 and we then could create the AutoIt version from it.

Regards

water

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

water, this looks really nice and it is very close to the solution I pictured and had hoped for. I'm impressed that it was possible for you to build the solution within the existing Excel Chart UDF framework - but this just shows the power of the UDF you have made.

Looking at the screen shot you posted it made me realize why I see R2 below H on some of the bars (which I refered to in my point 2) some 2 posts ago). It seems that the R2 lines refer to the price axis to the right, while OHLC bars refers to the price axis on the left. The 2 price axises are not alligned - e.g. try and look at the value 69. How can they be aligned? Typically the S2 and R2 values will contain the min and max values for a bar, but the L and H can go through these values making them the min and the max values. The aligment needs to take this into acount. I guess what I'm trying to say is, that I would like to avoid that the S2 or R2 is not shown in the chart, because the price axis has been aligned to the L and H (and vice versa). I don't know if this poses any challenges in the alignment - except that I don't know how such an aligment is done.

Share this post


Link to post
Share on other sites

Hi Saxo,

To align the primary and secondary axes this works:

; Align primary and secondary Y-axis
_XLChart_AxisSet($oChart1.Axes($xlValue, 2), $oChart1.Axes($xlValue).MinimumScale, $oChart1.Axes($xlValue).MaximumScale)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

if you like you can remove the secondary Y-axis:

; Delete the secondary Y-axis
_XLChart_ObjectDelete($oChart1.Axes($xlValue, 2))

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Thanks water - I will try that. Could you poste the code that you refered to earlier ("... by removing the line and setting the markers size, type and data labels.")? Then I will try to complete my example with your code.

Share this post


Link to post
Share on other sites

Here is the complete code I added:

; Add a data series for R2
_XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C11:R6C11", "=Sheet1!K1")
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
; Line with markers
_XLChart_SeriesSet($oChart1.SeriesCollection(5), $xlLineMarkers)
; Set the size and the type of markers
_XLChart_MarkerSet($oChart1.SeriesCollection(5), 30, $xlMarkerStyleDash)
If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
; Set data labels to display the value
_XLChart_DatalabelSet($oChart1.SeriesCollection(5))
; Set the size of the values
_XLChart_FontSet($oChart1.SeriesCollection(5).Datalabels, Default, 8)
; Set data line to invisible
_XLChart_LineSet($oChart1.SeriesCollection(5), Default, Default, False, Default, Default, $xlSheetHidden)
; Align primary and secondary Y-axis
_XLChart_AxisSet($oChart1.Axes($xlValue, 2), $oChart1.Axes($xlValue).MinimumScale, $oChart1.Axes($xlValue).MaximumScale)
; Delete the secondary Y-axis
_XLChart_ObjectDelete($oChart1.Axes($xlValue, 2))

Column K has to look like this:

R2
69,1065
69,78425
69,6425
69,3715
69,793
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

water, this works really nice! Thank you so much for your help. I have 2 additional questions:

  • How do you set the thickness of the lines added to the chart?
  • Is it possible to call a function/method that will return the min and max value that was set on the Y-axis? In the current exampel it goes from 67 to 70.

Share this post


Link to post
Share on other sites

Hi Saxo,

Glad it gets better all the time!

  • The lines for R2 are markers on an invisible data series. Unfortunately you can only set the size of the marker. This is done with function call
    _XLChart_MarkerSet($oChart1.SeriesCollection(5), 30, $xlMarkerStyleDash)
    30 is the size and can be anywhere between 1 and 72.
  • Min and max values of the Y-Axis are already used to synchronize the primary and secondary Y-axis.
    _XLChart_AxisSet($oChart1.Axes($xlValue, 2), $oChart1.Axes($xlValue).MinimumScale, $oChart1.Axes($xlValue).MaximumScale)

    $oChart1.Axes($xlValue).MinimumScale is the minimum value of the primary Y-axis, $oChart1.Axes($xlValue).MaximumScale is the maximum value. To get the values for the secondary Y-axis replace ($xlValue ) with ($xlValue, 2)

Regards

water

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Hi water

  • Ok - no problem. Thx for the explanation.
  • Yes. The answer was already in the code - sorry for failing to see that.
For anybody interested I have pasted an updated version of the initial example. Note that D1 does not have any S2, S1, P, R1 & R2 calculated, because the values are based on the previous day's OHLC.

Once again water, thank you for your tremendous help in getting the example working just the way I wanted.

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 $iXLC_Debug = 2 ;(or = 1)
    Local $oExcel = _ExcelBookNew()
   ; Disable Screen updating (forced even when debugging is activated)
   _XLChart_ScreenUpdateSet($oExcel, 2)
    _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, "", 2, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R2C2:R2C5)/4", 3, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R3C2:R3C5)/4", 4, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R4C2:R4C5)/4", 5, 6)
    _ExcelWriteFormula($oExcel, "=SUM(R5C2:R5C5)/4", 6, 6)
    $oExcel.ActiveSheet.Columns(6).AutoFit
_ExcelWriteCell($oExcel, "S2=P-(H-L) ", 1, 7)
    _ExcelWriteFormula($oExcel, "", 2, 7)
    _ExcelWriteFormula($oExcel, "=R3C6-(R2C3-R2C4)", 3, 7)
    _ExcelWriteFormula($oExcel, "=R4C6-(R3C3-R3C4)", 4, 7)
    _ExcelWriteFormula($oExcel, "=R5C6-(R4C3-R4C4)", 5, 7)
    _ExcelWriteFormula($oExcel, "=R6C6-(R5C3-R5C4)", 6, 7)
$oExcel.ActiveSheet.Columns(7).AutoFit
_ExcelWriteCell($oExcel, "S1=2×P-H", 1, 8)
    _ExcelWriteFormula($oExcel, "", 2, 8)
    _ExcelWriteFormula($oExcel, "=2*R3C6-R2C3", 3, 8)
    _ExcelWriteFormula($oExcel, "=2*R4C6-R3C3", 4, 8)
    _ExcelWriteFormula($oExcel, "=2*R5C6-R4C3", 5, 8)
    _ExcelWriteFormula($oExcel, "=2*R6C6-R5C3", 6, 8)
$oExcel.ActiveSheet.Columns(8).AutoFit
_ExcelWriteCell($oExcel, "R1=2×P-L", 1, 9)
    _ExcelWriteFormula($oExcel, "", 2, 9)
    _ExcelWriteFormula($oExcel, "=2*R3C6-R2C4", 3, 9)
    _ExcelWriteFormula($oExcel, "=2*R4C6-R3C4", 4, 9)
    _ExcelWriteFormula($oExcel, "=2*R5C6-R4C4", 5, 9)
    _ExcelWriteFormula($oExcel, "=2*R6C6-R5C4", 6, 9)
    $oExcel.ActiveSheet.Columns(9).AutoFit
_ExcelWriteCell($oExcel, "R2=P+(H-L)", 1, 10)
    _ExcelWriteFormula($oExcel, "", 2, 10)
    _ExcelWriteFormula($oExcel, "=R3C6+(R2C3-R2C4)", 3, 10)
    _ExcelWriteFormula($oExcel, "=R4C6+(R3C3-R3C4)", 4, 10)
    _ExcelWriteFormula($oExcel, "=R5C6+(R4C3-R4C4)", 5, 10)
    _ExcelWriteFormula($oExcel, "=R6C6+(R5C3-R5C4)", 6, 10)
    $oExcel.ActiveSheet.Columns(10).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)
; 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)
    ; Add a data series for P
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C6:R6C6", "=Sheet1!F1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(5), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(5), 30, $xlMarkerStyleDash)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(5))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(5).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(5), Default, Default, False, Default, Default, $xlSheetHidden)
    ; Add a data series for S2
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C7:R6C7", "=Sheet1!G1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(6), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(6), 30, $xlMarkerStyleDash)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(6))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(6).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(6), Default, Default, False, Default, Default, $xlSheetHidden)
    ; Add a data series for S1
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C8:R6C8", "=Sheet1!H1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(7), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(7), 30, $xlMarkerStyleDash)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(7))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(7).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(7), Default, Default, False, Default, Default, $xlSheetHidden)
    ; Add a data series for R1
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C9:R6C9", "=Sheet1!I1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(8), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(8), 30, $xlMarkerStyleDash)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(8))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(8).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(8), Default, Default, False, Default, Default, $xlSheetHidden)
; Add a data series for R2
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C10:R6C10", "=Sheet1!J1")
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Line with markers
    _XLChart_SeriesSet($oChart1.SeriesCollection(9), $xlLineMarkers)
    ; Set the size and the type of markers
    _XLChart_MarkerSet($oChart1.SeriesCollection(9), 30, $xlMarkerStyleDash)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ; Set data labels to display the value
    _XLChart_DatalabelSet($oChart1.SeriesCollection(9))
    ; Set the size of the values
    _XLChart_FontSet($oChart1.SeriesCollection(9).Datalabels, Default, 8)
    ; Set data line to invisible
    _XLChart_LineSet($oChart1.SeriesCollection(9), Default, Default, False, Default, Default, $xlSheetHidden)
    ; Align primary and secondary Y-axis
    _XLChart_AxisSet($oChart1.Axes($xlValue, 2), $oChart1.Axes($xlValue).MinimumScale, $oChart1.Axes($xlValue).MaximumScale)
    ; Delete the secondary Y-axis
    _XLChart_ObjectDelete($oChart1.Axes($xlValue, 2))
    ; Enable Screen updating
    _XLChart_ScreenUpdateSet($oExcel, 1)
    ; get the values for the Y-axis
    ;MsgBox(0, "Y-axis minimum value", $oChart1.Axes($xlValue).MinimumScale)
    ;MsgBox(0, "Y-axis maximum value", $oChart1.Axes($xlValue).MaximumScale)
    ; Save chart as PNG
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 Saxo

Share this post


Link to post
Share on other sites

Glad you got it working the way you like.

That's another example that shows what can be done with our UDF.

Could you please add a screenshot of the chart to your post? A chart is worth a thousand words :oops:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

One thing I would remove are the entries for the open, high, low, close data series in the legend. They are meaningless because there is no visible relation to any data series.

Replace

Local $DataName[5] = [4, "=Sheet1!B1", "=Sheet1!C1", "=Sheet1!D1", "=Sheet1!E1"]
with
Local $DataName[5] = [4]
and the entries will be gone. Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

This is the "General Help and Support" thread for the ExcelChart UDF.

The UDF itself can be downloaded

So if you have any questions, suggestions or errors please post here.

I don't have the 'ExcelChartConstants.au3', Where can I download this file? thanks!

Share this post


Link to post
Share on other sites

It's part of the ZIP file you can download from page 1 of this thread.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

If anyone on this forum does use this UDF to create "non standard" charts we would like to hear about and even like to see some screenshots.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

from me too water? ;)

Just an example of a business chart, as you know, I work for an international group and one of the KPI charts is made with the magnificent chartview UDF post-42917-0-02133600-1365617689_thumb.p

The report is generated fully automated (via a weekly scheduled job) and distributed by mail

I blurred out some text data because the thing is for internal use only. But for the example...

Cheers

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

 

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By Jamestay97
      Hello! Thanks you for looking at my post
      **No source code I'm sorry work related can't copy information**
      I've been using autoit for about 1 year. 
      I'm having trouble automating a click on an internet explorer web page and I've tried a lot of examples from help pages and forums already. The object I'm trying to click on isnt always in the same spot so I can't use mouse click or control click, I have tried to use the different get collection options and clickbyname, or index or get object. I'm just struggling. 
      Description of object I'm trying to click -- 
      HTML Code looks like <a ng-click.. "Click Here" it appears it's just a click able object named "click here" that opens a hidden window by running a script inside the web page. I'm not able to grab the information from the window unless it's open so I have to automate this click somehow. 
       
      I understand it's difficult to assist without having something to look at, I apologize for that sincerely and appreciate and assistance and suggestions. 
    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By kingjacob90
      Hi
      So I am trying to click the green button, this button is not always in the same place. So fare I am trying to click it by finding the color but there is also something else with the same color on the screen (circled in yellow) that is causing issues. Is there a way to use the Title and Class of the window (can't be just the window as there are more than one with the same name).
      How does AutoIt Info get this information?

    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
×
×
  • Create New...