Jump to content
Sign in to follow this  
water

Excel Chart UDF

Excel Chart UDF  

26 members have voted

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

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


Recommended Posts

water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
guinness

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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Screenshot of the chart create function:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
GreenCan

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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Zedna

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
GreenCan

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  

  • Similar Content

    • caramen
      By caramen
      I watched _OL_ItemSend&_OL_ItemFind&_OL_ItemCreate in OutlookEx UDF but hmmm
       
      Since it use the test environnement i cant get the orders of the mail creation
       
      Can someone make me win some time of reading all exemples script and lead me how to do one ?
      That whould be lovely.
      Gnight
    • Tersion
      By Tersion
      Here the this wiki page with list of available UDFs for data compression. For my tasks I only need ZIP support, so I started looking at pure AutoIt UDFs without any 3rd party dlls. And found out that most of available realizations uses standard ("native method") Windows dll - "zipfldr.dll". So for now I chose ZIP UDF by wraithdu. I've tested it on Windows 7 (x64) and it seem works fine. But here the comment from another topic where user says that Windows 10 discontinued support of "zipfldr.dll". Now I confused. I don't have around any Windows 10 machine to tested it. So maybe someone could confirm or deny that? Or maybe would better to switch to UDF with 7zip dll?
      I need an advice...
    • TheSaint
      By TheSaint
      Here is the bare bones of a UDF I have started work on.
      Mostly just a proof of concept at this stage, and still need to add some functions and dress the UDF up a bit ... to look like a UDF ... though it has my own distinct styling, especially as I have never really developed a UDF before now .... used plenty and modified plenty though. I've even invented my own UDF variable naming convention, which I am sure some of you will be aghast at. I work with what feels best for me, but others are free to adapt if they wish.
      The idea is to emulate the simplicity of INI files, but gain the benefits of SQL.
      Two scripts are provided.
      (1) The UDF, a work in progress - SimpleSQL_UDF.au3
      (2) An example or testing script - UDF_Test.au3
      Another first for me, is creating a 2D array from scratch, never done that before, that I can recall ... never had a need, and even for 1 dimension arrays, for a long time now, I have just used _StringSplit to create them. So I needed a bit of a refresher course, which my good buddy @TheDcoder assisted me with ... not without some angst I might add. LOL
      SimpleSQL_UDF.zip  (12 downloads previously)
      (I have now completed all the functions I intended to. My next update will be a big improvement, bringing things more inline with my latest INItoSQL DB program changes.)
      Program requires the sqlite3.dll, not included, but easily enough obtained.
      Hopefully the usage is self-evident ... just change the Job number variable in the UDF_Test.au3 file to check the existing functions out.
      Enjoy!
      P.S. This is also related to a new program I have just finished and uploaded - INItoSQL DB
    • xtcislove
      By xtcislove
      Hello,
      as a start in Autoit i tried something i was missing since im using Autoit. 

      I build a custom MessageBox which has a large amount of custom options and which scales its size on the parameters you set. 

      Aviable Settings:
      -Title
      -Unlimited Buttons
      -Text Color (Buttons, Text)

      -Background Color (Msgbox, Buttons, Label) 

      -Button Timeout
      -Autoclose Timeout
      -Icon (Default, No Icon, Custom)

      -Label/ Button Style. 
      -Transparency

      I tried to keep this as close as i could to a Msgbox i was used too on my batch times.

      After i was ready i realised, @Melba23 probably build a way better msgbox which would have suit my needs enterly, anyway thanks to @Melba23 because i use his Stringsize UDF. 

       
       
      local $Message = _sMsgBox("Test", 6, "Continue?") if @extended <> -1 Then MsgBox(0, @extended, $Message&" Button pressed")  
      ScalingMessageBox.au3
    • Gowrisankar
      By Gowrisankar
      Dear members, 
      I am working on a project where, emails from outlook are to be read and moved to various folders within the mailbox, based on the content of the emails.
      I used the below code for moving mails. It works fine when I run it against individual mail ids. But when I run it on Shared mailbox, the mails are not moved to respective folders.
      _OL_ItemMove($oOutlook, $sEntryId, Default, $sDestinationFolder) The value of $sEntryId is saved in an excel report initially. The current process reads the $sEntryId from the excel and passes it to "_OL_ItemMove" statement.
      Requesting the guidance of the forum members in this issue.
×