Jump to content
Sign in to follow this  
water

Excel Chart UDF

Excel Chart UDF  

27 members have voted

  1. 1. Would you like to see such an UDF?

    • Yes
      23
    • No
      0
    • Not at the moment
      4


Recommended Posts

I'm going to create an UDF which will let you create/manipulate Excel Charts.

It will consist of one function to create a simple chart (you just have to specify the data area, the type of the graph to draw, title, position of the chart etc.) and a lot of functions to create/modify all aspects of the chart.

Anyone interested?

Edit: We have released the UDF

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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,

I would be interested indeed.

I have enhanced the example of mattschinkel and I believe this one will work.

I didn't test it in Office 2010 yet

There is still a lot to do however, and not the least in the error handling.

I hope this is a good starting point for an Excel chart UDF.

I also worked out an Excel Chart Type Constants (MSOfficeExcelChartTypeConstants.au3), herewith attached:

#cs
MSOffice Excel Chart Type Constants
 
REFERENCE
  1.MSDN Library: Excel 2007 Developer Reference | XlChartType Enumeration
  2.MSDN Library: Microsoft Graph Visual Basic Reference | Microsoft Graph Constants
  http://msdn.microsoft.com/en-us/library/aa221100%28v=office.11%29.aspx
 
by: GreenCan
#ce
 
; AREA
const $xl3DArea=-4098   ; 3D AREA
const $xl3DAreaStacked=78   ; 3D STACKED AREA
const $xl3DAreaStacked100=79   ; 3D 100% STACKED AREA
const $xlArea=1   ; AREA
const $xlAreaStacked=76   ; STACKED AREA
const $xxlAreaStacked100=77   ; 100% STACKED AREA
; BAR
Const $xl3DBarClustered=60   ; 3D CLUSTERED BAR
Const $xl3DBarStacked=61   ; 3D STACKED BAR
Const $xl3DBarStacked100=62   ; 3D 100% STACKED BAR
Const $xlBarClustered=57   ; CLUSTERED BAR
Const $xlBarStacked=58   ; STACKED BAR
Const $xlBarStacked100=59   ; 100% STACKED BAR
Const $xlConeBarClustered=102   ; CLUSTERED CONE BAR
Const $xlConeBarStacked=103   ; STACKED CONE BAR
Const $xlConeBarStacked100=104   ; 100% STACKED CONE BAR
Const $xlCylinderBarClustered=95   ; CLUSTERED CYLINDER BAR
Const $xlCylinderBarStacked=96   ; STACKED CYLINDER BAR
Const $xlCylinderBarStacked100=97   ; 100% STACKED CYLINDER BAR
Const $xlPyramidBarClustered=109   ; CLUSTERED PYRAMID BAR
Const $xlPyramidBarStacked=110   ; STACKED PYRAMID BAR
Const $xlPyramidBarStacked100=111   ; 100% STACKED PYRAMID BAR
; BUBBLE
Const $xlBubble3DEffect=87   ; 3D BUBBLE, BUBBLE WITH 3D EFFECTS
Const $xlBubble=15   ; BUBBLE
; COLUMN
Const $xl3DColumnClustered=54   ; 3D CLUSTERED COLUMN
;~ Const $xl3DColumn=-4100   ; 3D COLUMN      ==> already defined in Excel.au3
Const $xlConeCol=105   ; 3D CONE COLUMN
Const $xlCylinderCol=98   ; 3D CYLINDER COLUMN
Const $xlPyramidCol=112   ; 3D PYRAMID COLUMN
Const $xl3DColumnStacked=55   ; 3D STACKED COLUMN
Const $xl3DColumnStacked100=56   ; 3D 100% STACKED COLUMN
Const $xlColumnClustered=51   ; CLUSTERED COLUMN
Const $xlColumnStacked=52   ; STACKED COLUMN
Const $xlColumnStacked100=53   ; 100% STACKED COLUMN
Const $xlConeColClustered=99   ; CLUSTERED CONE COLUMN
Const $xlConeColStacked=100   ; STACKED CONE COLUMN
Const $xlConeColStacked100=101   ; 100% STACKED CONE COLUMN
Const $xlCylinderColClustered=92   ; CLUSTERED CYLINDER COLUMN
Const $xlCylinderColStacked=93   ; STACKED CYLINDER COLUMN
Const $xlCylinderColStacked100=94   ; 100% STACKED CYLINDER COLUMN
Const $xlPyramidColClustered=106   ; CLUSTERED PYRAMID COLUMN
Const $xlPyramidColStacked=107   ; STACKED PYRAMID COLUMN
Const $xlPyramidColStacked100=108   ; 100% STACKED PYRAMID COLUMN
; DOUGHNUT
Const $xlDoughnut=-4120   ; DOUGHNUT
Const $xlDoughnutExploded=80   ; EXPLODED DOUGHNUT
; LINE
Const $xl3DLine=-4101   ; 3D LINE
;~ Const $xlLine=4   ; LINE   ==> already defined in Excel.au3
Const $xlLineMarkers=65   ; LINE WITH MARKERS
Const $xlLineStacked=63   ; STACKED LINE
Const $xlLineStacked100=64   ; 100% STACKED LINE
Const $xlLineMarkersStacked=66   ; STACKED LINE WITH MARKERS
Const $xlLineMarkersStacked100=67   ; 100% STACKED LINE WITH MARKERS
; PIE
Const $xl3DPie=-4102   ; 3D PIE
Const $xl3DPieExploded=70   ; 3D EXPLODED PIE
Const $xlBarOfPie=71   ; BAR OF PIE
Const $xlPieExploded=69   ; EXPLODED PIE
Const $xlPie=5   ; PIE
Const $xlPieOfPie=68   ; PIE OF PIE
; RADAR
;~ Const $xlRadar=-4151   ; RADAR     ==> already defined in Excel.au3
Const $xlRadarFilled=82   ; FILLED RADAR
Const $xlRadarMarkers=81   ; RADAR WITH DATA MARKERS
; SCATTER
Const $xlXYScatter=-4169   ; SCATTER
Const $xlXYScatterLines=74   ; SCATTER WITH LINES
Const $xlXYScatterLinesNoMarkers=75   ; SCATTER WITH LINES AND NO DATA MARKERS
Const $xlXYScatterSmooth=72   ; SCATTER WITH SMOOTH LINES
Const $xlXYScatterSmoothNoMarkers=73   ; SCATTER WITH SMOOTH LINES AND NO DATA MARKERS
; STOCK
Const $xlStockHLC=88   ; STOCK HLC (HIGH-LOW-CLOSE)
Const $xlStockOHLC=89   ; STOCK OHLC (OPEN-HIGH-LOW-CLOSE)
Const $xlStockVHLC=90   ; STOCK VHLC (VOLUME-HIGH-LOW-CLOSE)
Const $xlStockVOHLC=91   ; STOCK VOHLC (VOLUME-OPEN-HIGH-LOW-CLOSE)
; SURFACE
Const $xlSurface=83   ; 3D SURFACE
Const $xlSurfaceWireframe=84   ; 3D SURFACE WIREFRAME
Const $xlSurfaceTopView=85   ; SURFACE TOP VIEW
Const $xlSurfaceTopViewWireframe=86   ; SURFACE TOP VIEW WIREFRAME

Here is the udf and example:

#include <Excel.au3>
#include <MSOfficeExcelChartTypeConstants.au3>
 
; not defined in Excel.au3
Const $xlSeriesAxis = 3
 
Example()
Exit
 
 
Func Example()
; open excel file
$oExcel = _ExcelBookNew()
 
; create data
_ExcelWriteCell($oExcel, "Date", 1, 1)
_ExcelWriteCell($oExcel, "10/01/2011", 2, 1)
_ExcelWriteCell($oExcel, "10/02/2011", 3, 1)
_ExcelWriteCell($oExcel, "10/03/2011", 4, 1)
_ExcelWriteCell($oExcel, "10/04/2011", 5, 1)
_ExcelWriteCell($oExcel, "10/05/2011", 6, 1)
$oExcel.ActiveSheet.Columns(1).AutoFit
_ExcelWriteCell($oExcel, "Sales Store 1", 1, 2)
_ExcelWriteCell($oExcel, "10", 2, 2)
_ExcelWriteCell($oExcel, "23", 3, 2)
_ExcelWriteCell($oExcel, "15", 4, 2)
_ExcelWriteCell($oExcel, "20", 5, 2)
_ExcelWriteCell($oExcel, "34", 6, 2)
$oExcel.ActiveSheet.Columns(2).AutoFit
_ExcelWriteCell($oExcel, "Sales Store 2", 1, 3)
_ExcelWriteCell($oExcel, "18", 2, 3)
_ExcelWriteCell($oExcel, "23", 3, 3)
_ExcelWriteCell($oExcel, "40", 4, 3)
_ExcelWriteCell($oExcel, "32", 5, 3)
_ExcelWriteCell($oExcel, "28", 6, 3)
$oExcel.ActiveSheet.Columns(3).AutoFit
; Example 1: stacked bar chart - requires using arrays for $DataName and $DataRange (multiple data lines)
$Graph_position = "B2:K24"
$XValueRange = "=Sheet1!R2C1:R6C1"
Dim $_DataRange[3]=[2,"=Sheet1!R2C2:R6C2","=Sheet1!R2C3:R6C3"]
Dim $_DataName [3]=[2,"=Sheet1!R1C2:R1C2","=Sheet1!R1C3:R1C3"]
$iWorksheet = 2
_CreateExcelChart($oExcel, $iWorksheet, "Sales", $xl3DColumn, $Graph_position, $XValueRange, $_DataRange, $_DataName, 0, "Date", "Location", "Quantity")
; Example 2: example with one data Column
$Graph_position = "B26:K45"
$XValueRange = "=Sheet1!R1C1:R6C1"
$DataRange = "=Sheet1!R2C2:R6C2"
$_DataName = "=Sheet1!R1C2:R1C2"
$Title = _ExcelReadCell($oExcel, "B1")
  _CreateExcelChart($oExcel, $iWorksheet, $Title , $xl3DPieExploded, $Graph_position, $XValueRange, $DataRange, $_DataName)
 
; set the names of the worksheets
_ExcelSheetNameSet($oExcel, "Data")
_ExcelSheetActivate($oExcel, $iWorksheet)
_ExcelSheetNameSet($oExcel, "Graph")
Return
EndFunc ;==> Example
 
; #FUNCTION# ====================================================================================================
; Name...........:  _CreateExcelChart
; Description....:  Create a graph win Excel
; Syntax.........:  _CreateExcelChart( $oExcel,  $iWorksheet,  $sTitle ,  $iChartType,  $sSizeByCells,  $XValueRange,  $DataRange, $DataName, $iLegend = 1, $iXTitle = "", $iYTitle = "", $iZTitle = "")
; Parameters ....:  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;       $iWorksheet - Worksheet number to use for the graph creation (eg. 1)
;                  $sTitle - [Optional] - Chart Title
;        If empty, no title will be displayed
;       $iChartType - Chart type Number to be used ( see MSOfficeExcelChartTypeConstants.au3 for details, eg. $xl3DColumn)
;       $sSizeByCells - The LH top and RH bottom corner of the Graph (eg. "B2:K24")
;    $XValueRange - Category (X) axis label range
;      always a single range (eg. "=Sheet1!R2C1:R6C1")
;    $DataRange - The values range
;       either a single range (eg. "=Sheet1!R2C2:R6C2")
;      or an array (eg. Dim $_DataRange[3]=[2,"=Sheet1!R2C2:R6C2","=Sheet1!R2C3:R6C3"])
;      Note: Element 0 of the array is required but not used
;    $DataName - Header Name of the range
;       either a single range (eg. "=Sheet1!R1C2:R1C2")
;      or an array (eg. Dim $_DataName [3]=[2,"=Sheet1!R1C2:R1C2","=Sheet1!R1C3:R1C3"])
;      Note: Element 0 of the array is required but not used
;    $iLegend - [Optional] 1 is show legend (Default)
;          0 hide legend
;    $iXTitle - [Optional] X Axis title (Default = Off)
;    $iYTitle - [Optional] Y Axis title (Default = Off)
;    $iZTitle - [Optional] Y Axis title (Default = Off)
;
; Return values..:  none
;
; Authors........:  GreenCan
;
; Example........: Yes
; Remarks........:
; ===============================================================================================================
Func _CreateExcelChart( $oExcel,  $iWorksheet,  $sTitle,  $iChartType,  $SizeByCells,  $XValueRange,  $DataRange, $DataName, $iLegend = 1, $iXTitle = "", $iYTitle = "", $iZTitle = "")
$PlotBy = 2
; activate worksheet
$oSheet = $oExcel.Worksheets ($iWorksheet)
Local $oChartRange;- The range where you want the chart
Local $oNewChart ;- The ChartObject itself
$oChartRange = $oSheet.Range($SizeByCells)
$oNewChart = $oSheet.ChartObjects.Add($oChartRange.Left, $oChartRange.Top, $oChartRange.Width, $oChartRange.Height)
$oChart = $oNewChart.Chart
 
With $oChart
  .ChartType = $iChartType
  .SetSourceData ($oExcel.Worksheets(2).Range("A1:A1"), $PlotBy )
EndWith
 
If $oChart.SeriesCollection.Count = 0 Then
   $oChart.SeriesCollection.NewSeries
EndIf
;give an error "Both $DataRange & $DataName must be same type"
If (IsArray($DataName) And Not(IsArray($DataRange))) Or (Not(IsArray($DataName)) And IsArray($DataRange)) Then
   ;MsgBox(4096, "", "Both $DataRange & $DataName must be the same type")
   ConsoleWrite("Both $DataRange & $DataName must be the same type" & @CR)
   SetError(1)
   Return
EndIf
If IsArray($DataRange) Then
   $oChart.SeriesCollection(1).Delete
   For $i = 1 To UBound($DataName)-1
    $oChart.SeriesCollection.NewSeries
    With $oChart.SeriesCollection($i)
  .Name = $DataName[$i] ;- set name of values
  .XValues = $XValueRange ; X values ;R=row, C=colunm
  .Values = $DataRange[$i]
    EndWith
   Next
Else
   With $oChart.SeriesCollection(1)
    .Name = $DataName  ;- set name of values
    .XValues = $XValueRange ; X values ;R=row, C=colunm
    .Values = $DataRange
   EndWith
  EndIf
 
With $oChart
  ;.PlotArea.Select
  .HasDataTable = False
 
  ; Chart title
  If $sTitle <> "" Then
   .HasTitle = 1
   .ChartTitle.Characters.Text = $sTitle ;- set name of chart
  Else
   .HasTitle = 0
  EndIf
  ; legend
  If $iLegend = 0 Then
   .HasLegend = 0
  EndIf
  ; X Axis title (1)
  If $iXTitle <> "" Then
   .Axes($xlCategory).HasTitle = 1
   .Axes($xlCategory).AxisTitle.Characters.Text = $iXTitle
  EndIf
  ; Z Axis title (2)
  If $iZTitle <> "" Then
   .Axes($xlValue).HasTitle = 1
   .Axes($xlValue).AxisTitle.Characters.Text = $iZTitle
  EndIf
  ; Y Axis title (3)
  If $iYTitle <> "" Then
   .Axes($xlSeriesAxis).HasTitle = 1
   .Axes($xlSeriesAxis).AxisTitle.Characters.Text = $iYTitle
  EndIf
 
EndWith
Return
EndFunc ;==>_CreateExcelChart

The example shows how to create 2 graph, one 3D Column with 2 rows of data and one with an exploded Pie with one row of data.

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

Hi GreenCan,

thanks a lot for the code!

I hope to start the design phase for the UDF quite soon. I will post the list of functions and a description for each before starting to code.

So we can discuss the design and see if something is missing etc.

Water


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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

The "improvements" made in Office 2007 are so drastic that a lot of features from earlier Office versions do not work anymore or work differently.

Please check:

Excel 97-2003 features that are not supported in Office Excel 2007 or

What happened to my favorite charting features?

To reduce complexity of the UDF I'm only going to support Excel 2007 and later.

What do you think? I this a problem for you?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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

What do you think?

Mainstream support for Office 2003 application ended in April 2009 and extended support ends in April 2014

From wikipedia about Office 2003 and future updates. I think it's suffice to say Excel 2007 is a good choice.

For those interested ... extended support is obtained when a company signs an exclusive contract with Microsoft to support the for mentioned product for a limited time.


UDF List:

 
_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_ArrayFilter/_ArrayReduce_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 22/04/2018

Share this post


Link to post
Share on other sites

In July I asked a similar regarding Outlook. Not too many votes but I got the impression that many users still work with Outlook 2003.

I just want to get sure that people don't get disappointed when their old Excel version isn't supported.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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

Yes, the code that I posted on 4 Nov #2 only works with 2003. It crashed on a 2007. I didn't have the time to check the reason why it crashed.

And yes, I am using 2003 still but will move forward to 2010 soon.

I guess, a 2007+ UDF should suffice.


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

Hi GreenCan,

thanks for the reply.

I was able to make it work with Excel 2010. I'm going to implement the function for chart creation plus one example function to work with the chart title.

I've added some error checking plus a COM error handler.

I hope I can post a preview quite soon :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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 MPH,

I fear that the Excel Chart UDF will become quite large. OutlookEX has ~5600 lines, the Active Directory UDF ~4500 lines.

The Excel UDF is an "official" UDF that just needs to be brushed up a bit, whereas the Chart functionality is still in design phase and needs at least a year before the first "production" version can be released.

Just my 0.02$


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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

Screenshot of the chart create function:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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

A first try. The UDF plus some example scripts:

ExcelChart.au3 - The UDF

_XLChart_AxisSet.au3 - Set the properties of the selected Axis

_XLChart_ChartCreate.au3 - Creates 3 charts on worksheet 1 together with the data plus 1 chart on a separate chart sheet

_XLChart_FontSet.au3 - Changes the font settings of the title in chart 1 and the x-axis title in chart 2

_XLChart_TicksSet.au3 - Set tick marks and gridlines of a chart.

_XLChart_TitleGet.au3 - Return information about a title. This can be the chart or any axis title.

_XLChart_TitleSet.au3 - Set properties of a title. This can be the chart or any axis title.

Functions painted in orange are new.

What do you think?

We released the UDF

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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,

A quick check on 2003...

_XLChart_ChartCreate.au3: The chart "A28:F46" is empty.

_XLChart_FontSet.au3 works fine.

I have to dig in the problem but I leave on a business trip for the next 2 days. I will try to get more details by Friday.


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

Hi GreenCan,

Thanks for testing!

Glad most of the charts work with Excel 2003. As the COM error handler is already implemented you could insert

$iXLC_Debug = 2
at the top of the script and you will get detailed error information in a MsgBox.

And don't hurry! We have all the time in the world to create this UDF :D

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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,

These are the errors in xl2003:

COM Error Encountered in _XLChart_ChartCreate.au3
ExcelChart UDF version = 0.1.0
Scriptline = 199
NumberHex = 80020009
Number = -2147352567
WinDescription =
Description = Unable to set the Name property of the Series class
Source = Microsoft Excel
HelpFile = C:\Program Files\Microsoft Office\Office\1033\xlmain9.chm
HelpContext = 0
LastDllError = 0

However, I wouldn't worry about these, as previously we decided to focus on Excel 2007+, I would not try to resolve these.

++++

I have written a new function _XLChart_TickLabelPosition

#include-once
#include <excel.au3>
; Graph - interface constants
; Axis labels
Global Const $xlNextToAxis = 4 ;
; Global Const $xlNone = -4142  ; <== Already defined in Excel.au3
Global Const $xlHigh = -4127
Global Const $xlLow = -4134

#cs
; Yet unused Graph - interface constants
Global Const $xlAll = -4104
Global Const $xlBoth = 1
Global Const $xlChecker = 9
Global Const $xlCircle = 8
Global Const $xlCorner = 2
Global Const $xlCrissCross = 16
Global Const $xlCross = 4
Global Const $xlDiamond = 2
Global Const $xlDistributed = -4117
Global Const $xlDoubleAccounting = 5
Global Const $xlFixedValue = 1
Global Const $xlFormats = -4122
Global Const $xlGray16 = 17
Global Const $xlGray8 = 18
Global Const $xlGrid = 15
Global Const $xlInside = 2
Global Const $xlJustify = -4130
Global Const $xlLightDown = 13
Global Const $xlLightHorizontal = 11
Global Const $xlLightUp = 14
Global Const $xlLightVertical = 12
Global Const $xlManual = -4135
Global Const $xlMinusValues = 3
Global Const $xlModule = -4141
Global Const $xlOff = -4146
Global Const $xlOn = 1
Global Const $xlPercent = 2
Global Const $xlPlus = 9
Global Const $xlPlusValues = 2
Global Const $xlSemiGray75 = 10
Global Const $xlShowLabel = 4
Global Const $xlShowLabelAndPercent = 5
Global Const $xlShowPercent = 3
Global Const $xlShowValue = 2
Global Const $xlSimple = -4154
Global Const $xlSingle = 2
Global Const $xlSingleAccounting = 4
Global Const $xlSolid = 1
Global Const $xlSquare = 1
Global Const $xlStar = 5
Global Const $xlStError = 4
Global Const $xlToolbarButton = 2
Global Const $xlTriangle = 3
Global Const $xlGray25 = -4124
Global Const $xlGray50 = -4125
Global Const $xlGray75 = -4126
Global Const $xl3DBar = -4099
Global Const $xl3DSurface = -4103
Global Const $xlBar = 2
Global Const $xlColumn = 3
Global Const $xlCombination = -4111
Global Const $xlCustom = -4114
Global Const $xlDefaultAutoFormat = -1
Global Const $xlMaximum = 2
Global Const $xlMinimum = 4
Global Const $xlOpaque = 3
Global Const $xlTransparent = 2
Global Const $xlBidi = -5000
Global Const $xlLatin = -5001
Global Const $xlContext = -5002
Global Const $xlLTR = -5003
Global Const $xlRTL = -5004
Global Const $xlFullScript = 1
Global Const $xlPartialScript = 2
Global Const $xlMixedScript = 3
Global Const $xlMixedAuthorizedScript = 4
Global Const $xlDefault = -4143
Global Const $xlVisualCursor = 2
Global Const $xlLogicalCursor = 1
Global Const $xlSystem = 1
Global Const $xlPartial = 3
Global Const $xlHindiNumerals = 3
Global Const $xlBidiCalendar = 3
Global Const $xlGregorian = 2
Global Const $xlComplete = 4
Global Const $xlScale = 3
Global Const $xlWizardDisplayAlways = 1
Global Const $xlWizardDisplayDefault = 0
Global Const $xlWizardDisplayNever = 2
#ce

; #FUNCTION# ====================================================================================================
; Name...........: _XLChart_TickLabelPosition
; Description....: Set TickLabel Position for the specified object.
; Syntax.........: _XLChart_FontSet($oXLC_Object, $bXLC_Visible = Default)
; Parameters ....: $oXLC_Object  - Object for which the font properties should be set (ChartTitle, AxisTitle ...)
;                 $bXLC_Visible - If True the Ticklabel will not display ($xlNone)
;      other accepted values:  $xlHigh  - Above the graph
;           $xlNextToAxis -
;           $xlLow
; Return values .: Success - 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oXLC_Object is no object
; Authors........: GreenCan
; Modified ......:
; Remarks .......: Valid objects are:
;     Axes($xlSeriesAxis) ==> the Y Axis title
;     Axes(xlCategory)  ==> the X Axis title
;     Axes(xlValue)   ==> the Z Axis title
;   
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================
Func _XLChart_TickLabelPosition($oXLC_Object, $bXLC_Visible = Default)
If Not IsObj($oXLC_Object) Then Return SetError(1, 0, 0)

If $bXLC_Visible = Default Then
  $oXLC_Object.TickLabelPosition = $xlNone
Else
  $oXLC_Object.TickLabelPosition = $bXLC_Visible
EndIf
Return 1
EndFunc   ;==>_XLChart_TickLabelPosition

The series label in the example, second graph (sales store 1) should prefarably be hidden, that's what the function does.

Although not so useful, it can also hide or change the position of the other axe labels.

The script contains a new set of yet unused graph constants (put in comment)

This is the modified example script, the new function is example 5 (I permitted myself to put your both examples together, hope you don't mind...)

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

; *****************************************************************************
; Create example environment
; *****************************************************************************
Global $aExcel = _XLChart_Example()
If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _XLChart_Example!")
; *****************************************************************************
; Example 1
; 3D COLUMN: Without legend, with data table
; Requires arrays for $DataName and $DataRange
; *****************************************************************************
Global $Graph_position = "=_XLChart_Example!A8:F26"
Global $XValueRange = "=_XLChart_Example!R2C1:R6C1"
Global $DataRange[3] = [2, "=_XLChart_Example!R2C2:R6C2", "=_XLChart_Example!R2C3:R6C3"]
Global $DataName[3] = [2, "=_XLChart_Example!B1", "=_XLChart_Example!B1"]
Local $oChart1 = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DColumn, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date", "Location", "Quantity", True)
; *****************************************************************************
; Example 2
; 3D PYRAMID COLUMN: With legend
; Requires arrays for $DataName and $DataRange
; *****************************************************************************
$Graph_position = "H8:M26"
Local $oChart2 = _XLChart_ChartCreate($aExcel[0], 1, $xlPyramidCol, $Graph_position, "", $XValueRange, $DataRange, $DataName, True, "Sales", "Date", "", "Quantity")
; *****************************************************************************
; Example 3
; 3D STACKED AREA: Without legend
; *****************************************************************************
$Graph_position = "A28:F46"
Local $oChart3 = _XLChart_ChartCreate($aExcel[0], "_XLChart_Example", $xl3DAreaStacked, $Graph_position, "", $XValueRange, $DataRange, $DataName, False, "Sales", "Date")
; *****************************************************************************
; Example 4
; 3D EXPLODED PIE: With legend. Create the chart on a separate chart sheet
; *****************************************************************************
$Graph_position = "H28:M46"
$XValueRange = "=_XLChart_Example!R2C1:R6C1"
$DataRange = "=_XLChart_Example!R2C2:R6C2"
$DataName = "=_XLChart_Example!B1"
Global $Title = _ExcelReadCell($aExcel[0], "B1")
Local $oChart3 = _XLChart_ChartCreate($aExcel[0], 1, $xl3DPieExploded, 0, "Example Chart", $XValueRange, $DataRange, $DataName, True, $Title)
; *****************************************************************************
; Example 5
; Remove the Y Axis title (the series axis or $xlSeriesAxis)
; *****************************************************************************
_XLChart_TickLabelPosition($oChart2.Axes($xlSeriesAxis))

; *****************************************************************************
; Example 6
; Change title of Chart 1: Arial, 24, bold, italic, underline, magenta
; *****************************************************************************
_XLChart_FontSet($oChart1.ChartTitle, "Arial", 24, True, True, True, 0xFF00FF)
; *****************************************************************************
; Example 7
; Change x-Axis title of chart 2: Courier New, 36, bold, green
; *****************************************************************************
_XLChart_FontSet($oChart2.Axes($xlCategory).AxisTitle, "Courier New", 36, True, False, False, 0x00FF00)

For _XLChart_FontSet, I would suggest to add in remarks for which objects this function is valid, the same way that I did in _XLChart_TickLabelPosition.

This will make it more clear and useful for anyone who would use the udf in the future.

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

Hi GreenCan,

thanks for testing!

At the moment I try to shed some light on the structure of the Excel object model. Based on this structure we should group together the functionality of the UDF:

; A chart on its own sheet
Workbook
  Chart

; A chart on a worksheet
Workbook
  Worksheet
    Chartobject
      Chart

; For both types of charts
Chart
  Axes (Collection)
  ChartArea
  ChartTitle
  Datatable
  Floor
  Legend
  PageSetup
  PlotArea
  SeriesCollection (Collection)
    Series
      Border
      Points (Collection)
      Interior
  SideWall
  Walls

I intend to create two functions for every object. One to set the object, another to query the object.

This functions will be separated into two groups. One group with functions unique to a single object (e.g. only an axis has tick marks) and a second group of functions which can be used on multiple objects (e.g. _XLC_TitleSet sets the title for the chart and axis objects).

What do you think?

In a next step I'll try to list all functions which will handle axes. In one of this functions your _XLChart_TickLabelPosition function will be incorporated. How does this sound?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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

Yes, your approach makes sense.

Let me know if I can help with something.


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

Some kind of priority list would be fine.

What functions need to be in the UDF, what would be nice to have and what should only be done if we have very, very much spare time :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.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

I'm going to create an UDF which will let you create/manipulate Excel Charts.

It will consist of one function to create a simple chart (you just have to specify the data area, the type of the graph to draw, title, position of the chart etc.) and a lot of functions to create/modify all aspects of the chart.

Anyone interested?

I can use such function in one of my current projects.

So definitely YES.

EDIT: It would be awesome to be it compatible also with OpenOffice/LibreOffice which are FREEWARE, MS Office is not FREEWARE.

Edited by Zedna

Share this post


Link to post
Share on other sites

Water,

I'll work on this, give me a few days :D

Meanwhile, here's a small function that checks the installed Excel version, I guess that will be useful for compatibility issues

; *****************************************************************************
; Excel Version Check - Warning
; *****************************************************************************
Local $ExcelVersion = _XLversion()
If $ExcelVersion <> "Excel 2007" And $ExcelVersion <> "Excel 2010" Then MsgBox(48,"Warning only","The ExcelChart UDF is supporting Excel 2007 and 2010 only." & @CRLF & "The application will continue after this message",3)
; #FUNCTION# ====================================================================================================
; Name...........: _XLversion
; Description....: Returns the installed Excel version
; Syntax.........: _XLversion()
; Parameters ....: none
; Return values .: Success - Excel version
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oXLC_Object is no object
; Authors........: GreenCan
; Modified ......:
; Remarks .......: ExcelChart is written for Excel 2007 and 2010    
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================
Func _XLversion()
$oExcel = ObjCreate("Excel.Application")
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)

$ExcelVersion = $oExcel.Version
Select
  Case $ExcelVersion = "5.0"
   Return "Excel 5"
  Case $ExcelVersion = "7.0"
   Return "Excel 95"
  Case $ExcelVersion = "8.0"
   Return "Excel 97"
  Case $ExcelVersion = "9.0"
   Return "Excel 2000"
  Case $ExcelVersion = "10.0"
   Return "Excel 2002"
  Case $ExcelVersion = "11.0"
   Return "Excel 2003"
  Case $ExcelVersion = "12.0"
   Return "Excel 2007"
  Case $ExcelVersion = "14.0"
   Return "Excel 2010"  
  Case Else
   Return "Unknown version"
EndSelect
EndFunc   ;==>_XLversion

@Zedna

I have OpenOffice on my PC and I just installed Excel 2007 for this project :oops: , concentrating on Excel will already require some efforts.

Working on OpenOffice.org API charts could be a future project but there is still a lot of work to do in the OOoCOM_UDF (only 9 functions defined?, the basic functions are not yet complete)

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By mLipok
      This UDF was created for give any/some kind of support for GDPR solutions in AutoIt.
      This is a modest attempt at implementation.
       
      More details in support topic:
       
    • By mLipok
      This UDF was created for give any kind of support for GDPR solutions in AutoIt.
       
      #include "GDPR.au3" #AutoIt3Wrapper_Run_AU3Check=Y #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7 #Tidy_Parameters=/sort_funcs /reel ; #AutoIt3Wrapper_Run_Debug_Mode=Y _Example() Func _Example() _GDPR_Crypter_Wrapper(_Example_Crypter) Local $sText = 'AutoIt v3 is a freeware BASIC-like scripting language designed for automating the Windows GUI and general scripting.' MsgBox(0, @ScriptLineNumber, _GDPR_Anonymize_String($sText)) Local $dCrypted = _GDPR_Crypt_String($sText) MsgBox(0, @ScriptLineNumber, $dCrypted & @CRLF & @error & @CRLF & @extended) Local $sDecrypted = _GDPR_DeCrypt_AsString($dCrypted) MsgBox(0, @ScriptLineNumber, $sDecrypted & @CRLF & @error & @CRLF & @extended) _GDPR_Crypt_File(@ScriptFullPath, @ScriptFullPath & '.CRYPTED') If @error Then ConsoleWrite('! ---> @error=' & @error & ' @extended=' & @extended & ' : _GDPR_Crypt_File' & @CRLF) _GDPR_DeCrypt_File(@ScriptFullPath & '.CRYPTED', @ScriptFullPath & '.DECRYPTED.au3') If @error Then ConsoleWrite('! ---> @error=' & @error & ' @extended=' & @extended & ' : _GDPR_DeCrypt_File' & @CRLF) EndFunc ;==>_Example Func _Example_Crypter($dBinaryData, $bDataAlreadyEncrypted) _Crypt_Startup() ; Start the Crypt library. Local $dResult If $bDataAlreadyEncrypted Then $dResult = _Crypt_DecryptData($dBinaryData, 'securepassword', $CALG_3DES) ; Decrypt the data using the generic password string. The return value is a binary string. Else $dResult = _Crypt_EncryptData($dBinaryData, 'securepassword', $CALG_3DES) ; Encrypt the text with the new cryptographic key. EndIf _Crypt_Shutdown() ; Shutdown the Crypt library. Return $dResult EndFunc ;==>_Example_Crypter  
      Download link:
       
      WIKI:
      This UDF was added here:  https://www.autoitscript.com/wiki/User_Defined_Functions
       
       
    • By mLipok
      This UDF was created to facilitate the saving and reading of email configuration.
      Thanks to @water, @jchd, @Jos for helping in translation of _EmailConfig_GUI_Preset_**()
      Hope to have _EmailConfig_GUI_Preset_ES() version soon.

      If you want to create your national version of _EmailConfig_GUI_Preset_**() please do not hesitate ... contribute.
      _EmailConfig_GUI_Preset_EN() is translated by me and Google Translator (from my national Polish language), so if you have any fix for this please do not hesitate ... contribute.
       
      The EmailConfig_Example_STMP_Mailer.au3   is using modified version of Jos SMTP Mailer UDF
       
      #AutoIt3Wrapper_UseX64=N #AutoIt3Wrapper_Run_AU3Check=Y #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7 #Tidy_Parameters=/sort_funcs /reel #include <Array.au3> #include <AutoItConstants.au3> #include <ButtonConstants.au3> #include <ComboConstants.au3> #include <Crypt.au3> #include <EditConstants.au3> #include <File.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include "EmailConfig.au3" #include "GDPR.au3" ; https://www.autoitscript.com/forum/files/file/509-gdpr/ Global $oMyRet[2] #Region - EXAMPLE _MY_EXAMPLE__EmailConfig() Func _MY_EXAMPLE__EmailConfig() _GDPR_Crypter_Wrapper(_EmailConfig_ExampleCrypter) _EmailConfig_GUI_Preset_EN() ;~ _EmailConfig_GUI_Preset_DE() ;~ _EmailConfig_GUI_Preset_FR() ;~ _EmailConfig_GUI_Preset_NL() ;~ _EmailConfig_GUI_Preset_PL() _EmailConfig_SaveWrapper(_EmailConfig_SaveToINI) _EmailConfig_LoadWrapper(_EmailConfig_LoadFromINI) _EmailConfig_LoadWrapper() If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, 'Question #' & @ScriptLineNumber, _ 'Do you want to set email configuration ?') Then _EmailConfig_ShowGUI() EndIf Local $s_ToAddress = 'whereisyourdestination@your.email.com' Local $s_Subject = 'Testing email sending : ' & @YEAR & @MON & @MDAY & ' ' & @HOUR & @MIN & @SEC Local $s_Body = 'This is only a test' Local $s_Attachments = '' _SMTP_SendEmail_Example($s_ToAddress, $s_Subject, $s_Body, $s_Attachments) EndFunc ;==>_MY_EXAMPLE__EmailConfig Func _EmailConfig_ExampleCrypter($dBinaryData, $bDataAlreadyEncrypted) _Crypt_Startup() ; Start the Crypt library. Local $dResult If $bDataAlreadyEncrypted Then $dResult = _Crypt_DecryptData($dBinaryData, 'securepassword', $CALG_AES_256) ; Decrypt the data using the generic password string. The return value is a binary string. Else $dResult = _Crypt_EncryptData($dBinaryData, 'securepassword', $CALG_AES_256) ; Encrypt the text with the new cryptographic key. EndIf _Crypt_Shutdown() ; Shutdown the Crypt library. Return $dResult EndFunc ;==>_EmailConfig_ExampleCrypter #EndRegion - EXAMPLE ; ; The UDF Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_AttachFiles = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance = "Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $oCOM_Error_Handler = ObjEvent("AutoIt.Error", "MyErrFunc") #forceref $oCOM_Error_Handler Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 #forceref $i_Error Local $i_Error_desciption = "" #forceref $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 If $s_AttachFiles <> "" Then Local $S_Files2Attach = StringSplit($s_AttachFiles, ";") For $x = 1 To $S_Files2Attach[0] $S_Files2Attach[$x] = _PathFull($S_Files2Attach[$x]) ;~ ConsoleWrite('@@ Debug : $S_Files2Attach[$x] = ' & $S_Files2Attach[$x] & @LF & '>Error code: ' & @error & @LF) ;### Debug Console If FileExists($S_Files2Attach[$x]) Then ConsoleWrite('+> File attachment added: ' & $S_Files2Attach[$x] & @LF) $objEmail.AddAttachment($S_Files2Attach[$x]) Else ConsoleWrite('!> File not found to attach: ' & $S_Files2Attach[$x] & @LF) SetError(1) Return 0 EndIf Next 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 If @error Then SetError(2) Return $oMyRet[1] EndIf $objEmail = "" EndFunc ;==>_INetSmtpMailCom Func _SMTP_SendEmail_Example($s_ToAddress, $s_Subject, $s_Body, $s_Attachments) #Tidy_ILC_Pos=120 Local $aEMAIL_CONFIG = __EmailConfig__API() Local $SmtpServer = $aEMAIL_CONFIG[$EMAIL_CONFIG__22__SMTP_SERVER_NAME][$EMAIL_CONFIG__COL3_SAVELOAD_VALUE] ; address for the smtp-server to use - REQUIRED Local $FromName = $aEMAIL_CONFIG[$EMAIL_CONFIG__02__COMMON_NAME][$EMAIL_CONFIG__COL3_SAVELOAD_VALUE] ; name from who the email was sent Local $FromAddress = $aEMAIL_CONFIG[$EMAIL_CONFIG__03__EMAIL_ADDRESS][$EMAIL_CONFIG__COL3_SAVELOAD_VALUE] ; address from where the mail should come Local $ToAddress = $s_ToAddress ; destination address of the email - REQUIRED Local $Subject = $s_Subject ; subject from the email - can be anything you want it to be Local $Body = $s_Body ; the messagebody from the mail - can be left blank but then you get a blank mail Local $AttachFiles = $s_Attachments ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed Local $CcAddress = "" ; address for cc - leave blank if not needed Local $BccAddress = "" ; address for bcc - leave blank if not needed Local $Importance = "Normal" ; Send message priority: "High", "Normal", "Low" Local $Username = $aEMAIL_CONFIG[$EMAIL_CONFIG__20__SMTP_USER_NAME][$EMAIL_CONFIG__COL3_SAVELOAD_VALUE] ; username for the account used from where the mail gets sent - REQUIRED Local $Password = $aEMAIL_CONFIG[$EMAIL_CONFIG__21__SMTP_PASSWORD][$EMAIL_CONFIG__COL3_SAVELOAD_VALUE] ; password for the account used from where the mail gets sent - REQUIRED Local $IPPort = $aEMAIL_CONFIG[$EMAIL_CONFIG__23__SMTP_PORT_NUMBER][$EMAIL_CONFIG__COL3_SAVELOAD_VALUE] ; port used for sending the mail ; in many country port 25 is not recomended, in such case use 587 instead Local $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS Local $tls = 0 ; enables/disables TLS when required ;~ Local $IPPort = 465 ; GMAIL port used for sending the mail ;~ Local $ssl = 1 ; GMAIL enables/disables secure socket layer sending - put to 1 if using httpS Local $rc = _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $AttachFiles, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) If @error Then MsgBox(0, "Error sending email", "Error code: " & @error & @CRLF & "Description: " & $rc) EndIf EndFunc ;==>_SMTP_SendEmail_Example ; ; ; Com Error Handler Func MyErrFunc(ByRef $oMyError) Local $HexNumber = Hex($oMyError.number, 8) $oMyRet[0] = $HexNumber $oMyRet[1] = StringStripWS($oMyError.description, 3) ConsoleWrite("### COM Error ! Number: " & $HexNumber & " ScriptLine: " & $oMyError.scriptline & " Description:" & $oMyRet[1] & @LF) SetError(1) ; something to check for when this function returns Return EndFunc ;==>MyErrFunc  

       
      EmailConfig.au3 EmailConfig_Example_STMP_Mailer.au3
    • By pat4005
      A tiny UDF that can shoot magic packets at your computers to wake them the heck up. All credits to Olish.
      The only parameter it needs to be specified – is your machine's MAC-address (ip address (the second parameter), at which you will be sending magic packet is generating automatically from @IPAddress1 macro)
      Example:
      _WoL_WakeDevice('001CC0CAED7A') ; the second parameter (if necessery) must be a broadcast address of your local network segment (i.e. 192.168.0.255 for a network 192.168.0.0)  
      _WakeOnLan.au3
    • By MrCreatoR
      This UDF allows to create formatted label using pseudo element RichLabel (RichEdit actually). Formating is set by using special modificator similar to <font> tag in Html.
      Notes: This UDF is a transformation-continuation of related UDF


      Example:
      Download:
      GUIRichLabel_1.2.zip
      Small syntax related fix: GUIRichLabel_1.1.zip
      GUIRichLabel_1.1.zip
       
      History version:
×
×
  • Create New...