Jump to content

ExcelChart UDF - Help & Support


Recommended Posts

I think this format may be easier to work with, but am still working on the_XLChart_ChartCreate() parameters.  

_ExcelWriteCell($oExcel, "first visit", 1, 2)
_ExcelWriteCell($oExcel, "yes", 2,1)
_ExcelWriteCell($oExcel, "no", 3,1)
_ExcelWriteArray($oExcel, 2, 2, $values, 1)
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

If you check the internal function _XLChart_Example in the UDF you can find an example for a 3D pie chart.

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

func pie()

    Local $oExcel = ObjCreate("Excel.Application")

    $oExcel.Visible = 1
    $oExcel.WorkBooks.Add
    $oExcel.ActiveWorkbook.Sheets(1).Select()
    
    $oExcel.Activesheet.Cells(1, 1).Value = "Answer"
    $oExcel.Activesheet.Cells(2, 1).Value = "Yes"
    $oExcel.Activesheet.Cells(3, 1).Value = "No"
    $oExcel.ActiveSheet.Columns(1).AutoFit
    $oExcel.Activesheet.Cells(1, 2).Value = "First Time"
    $oExcel.Activesheet.Cells(2, 2).Value = "52"
    $oExcel.Activesheet.Cells(3, 2).Value = "205"
    $oExcel.ActiveSheet.Columns(2).AutoFit
    
    ; Set the names of the worksheets
    $oExcel.ActiveSheet.Name = "_XLChart_Example"

    Local $XValueRange = "=_XLChart_Example!R2C1:R3C1"
    _XLChart_ChartCreate($oExcel, 1, $xl3DPie, "A9:D18", "", $XValueRange, "=_XLChart_Example!R2C2:R3C2", "=_XLChart_Example!B1", True, "First Time")

endfunc

pie()

I studied _XLChart_Example() and made the example above, which works.

R stands for row.

C stands for column.

Thanks,

-John

Edited by jftuga
Link to post
Share on other sites

:D

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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

Hi again. This UDF is awsome and i use it alot. However for my current projekt i need to be able to format the title Title.

I know i can chnage the Title with the _XLChart_TitleSet function, and that works great. However i also need to be able to use formating of the chart-title.

In my case i use this code:
 

_XLChart_TitleSet($array1[1][0].ChartTitle,"String1" & @CR & "String2")

String1 needs to be size 12 and "fat". String2 needs to be Size 8. Any idees?

I also need to be able to change the Size of the chart. If its 10cm in hight, i need to be able to change it based on the number of values i put in.
Thanx for any help/suggestions

Edited by Tjalve
Link to post
Share on other sites
_XLChart_TitleSet($array1[1][0].ChartTitle,"String1" & @CR & "String2")
String1 needs to be size 12 and "fat". String2 needs to be Size 8. Any idees?

 

How would you do it in pure Excel?

I also need to be able to change the Size of the chart. If its 10cm in hight, i need to be able to change it based on the number of values i put in.

Use function _XLChart_ChartPositionSet to resize and reposition a chart object

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

How would you do it in pure Excel?

Use function _XLChart_ChartPositionSet to resize and reposition a chart object

Great stuff. I will try.

One more thing. The "area" inside the chart that hold the chart itself (the bars) also need rizing. Is it posible to rixe thoose so that they are always the same number of pixels from the edge?

The VBA Code look like this when i changed it:

ActiveSheet.ChartObjects("Diagram 2").Activate
    ActiveChart.PlotArea.Select
    ActiveSheet.ChartObjects("Diagram 2").Activate
    Selection.Height = 57.948

Regarding the Title. I just mark the text and change the size of it. I tried to to it while recording a macro, but it doesnt record formating (or I did somthing wrong). I did find this VBA code. Perhaps you can make an sense of it.

 

With ActiveChart
        ActiveChart.ChartTitle.Select
        With Selection.Border
            .Weight = xlHairline
            .LineStyle = xlAutomatic
        End With
        Selection.Shadow = True
        Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=1
        With Selection
            .Fill.Visible = True
            .Fill.ForeColor.SchemeColor = 58
            .Fill.BackColor.SchemeColor = 57
        End With
        Selection.AutoScaleFont = True
        With Selection.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 18
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
            .Background = xlAutomatic
        End With
    End With  '  With ActiveChart
Link to post
Share on other sites

The "area" inside the chart that hold the chart itself (the bars) also need rizing. Is it posible to rixe thoose so that they are always the same number of pixels from the edge?

I would try function _XLChart_ObjectPositionSet and pass the PlotArea object to the function. The math needs to be done by you.

The VBA you provided alters a lot of properties for the WHOLE title. But there is no way I know of to only alter PART of the title.

Could you record a macro and select the first part of the title and then the second part ("String1 needs to be size 12 and "fat". String2 needs to be Size 8.")

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

i Thank you for you help water.

I tried selecting as you said and i just got this:

ActiveSheet.ChartObjects("Diagram 2").Activate
    ActiveChart.ChartTitle.Select
    ActiveSheet.ChartObjects("Diagram 2").Activate

I use a template so i might fix the formatting in th eoriginal template. But is there a way for me to "replace" some parts of the text and keep the original formatting?

In this case. I set String1 to 12 and fat. And String2 to 8. I then replace the text with the text i want? Is that possible?

Link to post
Share on other sites

I haven't done it myself and can't test at the moment but something like this should work:

$oTitle = $oWorkbook.ActiveSheet.ChartObjects("Diagram 2").ChartTitle ; Returns the chart title
$oString1 = $oTitle.Characters(Startposition, Length) ; Returns a substring of the chart title. Modify Startposition and Length
_XLChart_FontSet($oString1, ...) ; Set the font properties for the substring

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

 

I haven't done it myself and can't test at the moment but something like this should work:

$oTitle = $oWorkbook.ActiveSheet.ChartObjects("Diagram 2").ChartTitle ; Returns the chart title
$oString1 = $oTitle.Characters(Startposition, Length) ; Returns a substring of the chart title. Modify Startposition and Length
_XLChart_FontSet($oString1, ...) ; Set the font properties for the substring

HAHA you are a king! That works just pefectly! Thank you!

Edited by Tjalve
Link to post
Share on other sites

Glad to be of service :)

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • 1 year later...

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

Have fun testing!

For download please see my signature.

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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

Hello Water,

Hope you are well. I am trying to work with UNIX time on my x-axis and Excel plots it in descending order (when in fact UNIX time is ascending!).

Is there anyway to modify the plotting order in your UDF? I don't need example code, just need to know if the option exists and what it is (didn't find it in the UDF files).

Edit: The workaround I am using is to multiply the unix time value by -1... but that looks ugly and is a recipe for disaster by "best practice"

Edited by AlwaysLearning
Link to post
Share on other sites

Could you please provide a simple reproducer script so I can play with it?

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Hello Water

I will have to extract that portion of the script as there are private API keys involved and the function that executes this chart generation is dependent on several other functions in the script which use these API keys.

I can remove this and rebuild it but it will take me some time because I learned as I went making this and didn't manage my code very well. Half my mistakes were fixed with duct taping "if/then" and random variables (once I have everything functioning, I plan to rebuild it with less "workarounds" -- your UDF and others has helped me understand why a quick and sloppy fix can end up costing you time in the long run). 

For now I am attaching a photo which may be sufficient.

Notice on the x-axis that the UNIX timestamps are time passed in seconds since 1970.
As such, the most recent time is always the higher number. 
Excel's plotting order doesn't like this as it goes descending by default.

chart.jpg

Edit: extracting an example was easier than I thought!

Here is the code:

#include <String.au3>
#include <Array.au3>
#include <Inet.au3>
#include <Excel.au3>
#include <ExcelChart.au3>

Global $oExcelProcess = _Excel_Open() ;; ---- Set to false to make invisible
Global $iXLC_Debug = 2 ;(or = 1)
Global $oExcel = _Excel_BookNew($oExcelProcess)

$aChartData = ChartData()

#Region Chart Rendering ;; Render chart on first run to save time when refreshing the JPEG for GUI later
    _XLChart_ScreenUpdateSet($oExcel, 2)
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "Date", "A1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "O", "B1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "H", "C1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "L", "D1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "C", "E1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "P", "G1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "S2", "H1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "S1", "I1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "R2", "J1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "R1", "K1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, $aChartData, "A2")

    $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
    Global $oChart1 = _XLChart_ChartCreate($oExcel, $iWorksheet, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName)

    ; Format chart
    _XLChart_LayoutSet($oChart1, Default, 46)
    _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)
    ;_XLChart_FillSet($oChart1.ChartArea, 0xF0F0F0)
    ;If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)


    ; Add a data series for P
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C7:R" & (6) & "C7", "=Sheet1!G1")
    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), 7, $xlMarkerStyleDiamond, default, 0x0057e7)
    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!R2C8:R" & (6) & "C8", "=Sheet1!H1")
    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), 10, $xlMarkerStyleDash, Default, 0xC80014)
    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!R2C9:R" & (6) & "C9", "=Sheet1!I1")
    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), 10, $xlMarkerStyleDot, Default, 0xDE6672)
    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 R2
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C10:R" & (6) & "C10", "=Sheet1!J1")
    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), 10, $xlMarkerStyleDash, Default, 0x78c800)
    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 R1
    _XLChart_SeriesAdd($oChart1, $XValueRange, "=Sheet1!R2C11:R" & (6) & "C11", "=Sheet1!K1")
    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), 10, $xlMarkerStyleDot, Default, 0xc9e999)
    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 & "\chart.jpg"
    _XLChart_ChartExport($oChart1, $iFile, "JPEG", False, True)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
#EndRegion


While 1
    $aChartData = ChartData()
    GUIGraph($aChartData)
    Sleep ( 10 )
WEnd

Func ChartData($sParameters = '') ; ChartData -- Time, Open, High, Low, Close, Volume =========>

    $sChartData = _INetGetSource("https://coincheck.com/api/charts/candle_rates?limit=20&market=coincheck&pair=btc_jpy&unit=900&v2=true")
    $sChartData = StringTrimLeft($sChartData, 2)
    $sChartData = StringTrimRight($sChartData, 2)
    $sChartData = StringReplace($sChartData, '],[', @LF)
    $sChartData = StringReplace($sChartData, ',', " ")
    $aChartData = _VarTo2D($sChartData)

    ReDim $aChartData[UBound($aChartData)][UBound($aChartData, 2) + 5]

    ;; === Pivot Point
    $tot = 0
    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        For $j = 1 To 4 ; Column by column
            $tot += $aChartData[$i][$j] ; 1 * current cell = tot, then tot * next cell
        Next
        $tot = Round(($tot / 4), 0)
        $aChartData[$i][UBound($aChartData, 2) - 5] = $tot ; write the final total
        $tot = 0 ;reset and move to next row
    Next

    ;; === S1=P-(H-L)

    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 4] = $p - ($h - $l)
    Next

    ;; === S2= P + 2×P-H
    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 3] = $p - (2 * ($h - $p))
    Next

    ;; === R1
    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 2] = $p + (2 * ($p - $l))
    Next

    ;; === R2

    For $i = 0 To UBound($aChartData) - 1 ; Row by row
        Local $p = $aChartData[$i][UBound($aChartData, 2) - 5]
        Local $h = $aChartData[$i][2]
        Local $l = $aChartData[$i][3]
        $aChartData[$i][UBound($aChartData, 2) - 1] = $p + ($h - $l)
    Next


    ;_ArrayDisplay($aChartData)

    Return $aChartData

EndFunc   ;==>ChartData

Func GUIGraph($aChartData)

    ; Disable Screen updating (forced even when debugging is activated)
    _XLChart_ScreenUpdateSet($oExcel, 2)
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, "Date", "A1")
    _Excel_RangeWrite($oExcel, $oExcel.Activesheet, $aChartData, "A2")


    ; 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 JPEG ===> The GUI part is commented out because my GUI code isn't necessary here.
    _XLChart_ChartExport($oChart1,  @ScriptDir & "\chart.jpg", "JPEG", False, True)
    If @error Then ConsoleWrite("Line number: " & @ScriptLineNumber & " - Error: " & @error & @CR)
    ;GUICtrlSetImage($ChartImage, @ScriptDir & "\chart.jpg")
    Return
EndFunc   ;==>Example

Func _VarTo2D($var, $sSeparator = " ")
    Local $aRows = StringSplit(StringStripCR($var), @LF), $aColumns, $aResult[$aRows[0]][1]
    For $iRow = 1 To $aRows[0]
        $aColumns = StringSplit($aRows[$iRow], $sSeparator)
        If $aColumns[0] > UBound($aResult, 2) Then ReDim $aResult[$aRows[0]][$aColumns[0]]
        For $iColumn = 1 To $aColumns[0]
            $aResult[$iRow - 1][$iColumn - 1] = $aColumns[$iColumn]
        Next
    Next
    Return $aResult
EndFunc   ;==>_VarTo2D

Since we have the code there, one more issue I had was trying to change the GraphPosition based on Ubound($aChartData).
I actually wanted to change the Chart Data size by just clicking buttons in my GUI, but this would mean the GraphPosition and Graph Size would depend on the size of $aChartData.
While this works and generates the expected Excel Result, all my exported JPEGs/PNGs are 0KB unless I keep the Position of the chart relatively near the top of the spreadsheet.

Edited by AlwaysLearning
Edit1 : Adding code Edit 2: removing unnecessary #include UDFs
Link to post
Share on other sites

I created this little reproducer script and it works as expected:

#AutoIt3Wrapper_UseX64=n
#AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#include-once
#include <ExcelChart.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
$oExcel.ActiveSheet.Name = "_XLChart_Example"

; *****************************************************************************
; Example 4
; OPEN-HIGH-LOW-CLOSE Chart
; *****************************************************************************
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Time", "H1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517614200, "H2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517615100, "H3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517616000, "H4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517616900, "H5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, 1517617800, "H6")
$oExcel.ActiveSheet.Columns(8).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Open", "I1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.682", "I2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.342", "I3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.203", "I4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.204", "I5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.485", "I6")
$oExcel.ActiveSheet.Columns(9).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "High", "J1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.826", "J2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.375", "J3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.463", "J4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.364", "J5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.598", "J6")
$oExcel.ActiveSheet.Columns(10).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Low", "K1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.263", "K2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.654", "K3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.085", "K4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.891", "K5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.251", "K6")
$oExcel.ActiveSheet.Columns(11).AutoFit
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "Close", "L1")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.403", "L2")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.882", "L3")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.307", "L4")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "68.135", "L5")
_Excel_RangeWrite($oExcel.Activeworkbook, Default, "69.450", "L6")
$oExcel.ActiveSheet.Columns(12).AutoFit
Global $Graph_position = "A1:F19"
Global $XValueRange = "=_XLChart_Example!R2C8:R6C8"
Global $DataRange[5] = [4, "=_XLChart_Example!R2C9:R6C9", "=_XLChart_Example!R2C10:R6C10", "=_XLChart_Example!R2C11:R6C11", "=_XLChart_Example!R2C12:R6C12"]
Global $DataName[5] = [4, "=_XLChart_Example!O1", "=_XLChart_Example!P1", "=_XLChart_Example!Q1", "=_XLChart_Example!R1"]
Global $oChart1 = _XLChart_ChartCreate($oExcel, 1, $xlStockOHLC, $Graph_position, "", $XValueRange, $DataRange, $DataName, Default, "Open-High-Low-Close Chart")
If @error Then MsgBox(64, "Excel Chart Example Script", "Error " & @error & " returned by function '_XLChart_ChartCreate'! on line " & @ScriptLineNumber)
; 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), 250)   ; 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)

 

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Water, 

Unfortunately, reversing the order of the input data isn't an option.

In your example, you have fixed my issue by reversing the order of data inputted in column H (it is in ascending order which will of course work as expected -- that is the default operating mode).

However, my data comes from a live array fed in by an API in DESCENDING order.
I realize I could add a command to filter this into ascending order in excel, but that would make it very ineffective because the script is expected to cycle 5 times per second or more.

Essentially, I need to edit the chart's x-axis plotting order to Descending mode, which is a feature I don't see in the UDF.

Link to post
Share on other sites

Can't test at the moment but I think this could be the solution:

$oChart1.Axes($xlValue).ReversePlotOrder = True

 

My UDFs and Tutorials:

Spoiler

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

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to 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...