water Posted October 25, 2011 Share Posted October 25, 2011 (edited) 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 December 12, 2011 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
GreenCan Posted November 4, 2011 Share Posted November 4, 2011 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: expandcollapse popup#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: expandcollapse popup#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 Link to comment Share on other sites More sharing options...
water Posted November 4, 2011 Author Share Posted November 4, 2011 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 7, 2011 Author Share Posted November 7, 2011 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 orWhat 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
guinness Posted November 7, 2011 Share Posted November 7, 2011 What do you think?Mainstream support for Office 2003 application ended in April 2009 and extended support ends in April 2014From 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 parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples... Updated: 22/04/2018 Link to comment Share on other sites More sharing options...
water Posted November 7, 2011 Author Share Posted November 7, 2011 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
GreenCan Posted November 7, 2011 Share Posted November 7, 2011 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 Link to comment Share on other sites More sharing options...
water Posted November 7, 2011 Author Share Posted November 7, 2011 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Bert Posted November 7, 2011 Share Posted November 7, 2011 Hey Water, would it not be more "clean" to update excel.au3 to include what you have in mind? The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
water Posted November 7, 2011 Author Share Posted November 7, 2011 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 8, 2011 Author Share Posted November 8, 2011 Screenshot of the chart create function: My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 8, 2011 Author Share Posted November 8, 2011 (edited) 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 December 12, 2011 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
GreenCan Posted November 9, 2011 Share Posted November 9, 2011 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 Link to comment Share on other sites More sharing options...
water Posted November 9, 2011 Author Share Posted November 9, 2011 (edited) 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 = 2at 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 Edited November 9, 2011 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
GreenCan Posted November 11, 2011 Share Posted November 11, 2011 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 expandcollapse popup#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...) expandcollapse popup#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 Link to comment Share on other sites More sharing options...
water Posted November 11, 2011 Author Share Posted November 11, 2011 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
GreenCan Posted November 12, 2011 Share Posted November 12, 2011 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 Link to comment Share on other sites More sharing options...
water Posted November 12, 2011 Author Share Posted November 12, 2011 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Zedna Posted November 12, 2011 Share Posted November 12, 2011 (edited) 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 November 12, 2011 by Zedna Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
GreenCan Posted November 12, 2011 Share Posted November 12, 2011 Water, I'll work on this, give me a few days Meanwhile, here's a small function that checks the installed Excel version, I guess that will be useful for compatibility issues expandcollapse popup; ***************************************************************************** ; 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 , 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now