Sign in to follow this  
Followers 0
water

Excel Chart UDF

Excel Chart UDF   25 members have voted

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

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

Please sign in or register to vote in this poll.

26 posts in this topic

#1 ·  Posted (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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites



Water,

I would be interested indeed.

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

I didn't test it in Office 2010 yet

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

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

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

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

Here is the udf and example:

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

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

GreenCan


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Hi GreenCan,

thanks a lot for the code!

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

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

Water


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Please check:

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

What happened to my favorite charting features?

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

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

What do you think?

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

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

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


_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_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: 04/09/2015

Share this post


Link to post
Share on other sites

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

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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

I guess, a 2007+ UDF should suffice.


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Hi GreenCan,

thanks for the reply.

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

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

I hope I can post a preview quite soon :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi MPH,

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

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

Just my 0.02$


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Screenshot of the chart create function:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#12 ·  Posted (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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Water,

A quick check on 2003...

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

_XLChart_FontSet.au3 works fine.

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


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

#14 ·  Posted (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 = 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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi Water,

These are the errors in xl2003:

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

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

++++

I have written a new function _XLChart_TickLabelPosition

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

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

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

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

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

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

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

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

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

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

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

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

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

GreenCan


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Hi GreenCan,

thanks for testing!

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

; A chart on its own sheet
Workbook
  Chart

; A chart on a worksheet
Workbook
  Worksheet
    Chartobject
      Chart

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

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

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

What do you think?

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yes, your approach makes sense.

Let me know if I can help with something.


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Some kind of priority list would be fine.

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#19 ·  Posted (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 by Zedna

Share this post


Link to post
Share on other sites

Water,

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

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

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

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

@Zedna

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

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

GreenCan


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

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

Create an account

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


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning everyone
      I'm working on a little project, and, I encountered a little strange error when I try to add some data to an array...
      The code I wrote is this:
      Func _WMI_Get_Win32_TemperatureProbe($blnCanUseWMI, $blnCustomArrayDisplay = False, $blnReturnEU = False) If $blnCanUseWMI Then Local $objWMI_QueryResult = $objWMI.ExecQuery("SELECT * FROM Win32_TemperatureProbe", "WQL", 32) If @error Then __ConsoleWrite("Error executing the query on Win32_TemperatureProbe class.", @error, 9999) Else Local $arrWin32_TemperatureProbe[1][3] _ArrayDelete($arrWin32_TemperatureProbe, 0) If @error Then __ConsoleWrite("Error deleting the 0st element $arrWin32_TemperatureProbe array.", @error, 9999) Else Local $objWMI_Variable = Null, $strWMI_QueryResult = "", $i = 0 For $objWMI_Variable In $objWMI_QueryResult $strWMI_QueryResult &= "QUERY RESULT" & "|# " & $i & "|/" & @CRLF & _ "Accuracy" & "|" & $objWMI_Variable.Accuracy & "|" & "[sint32]" & @CRLF & _ "Availability" & "|" & $objWMI_Variable.Availability & "|" & "[uint16]" & @CRLF & _ "Caption" & "|" & $objWMI_Variable.Caption & "|" & "[string]" & @CRLF & _ "ConfigManagerErrorCode" & "|" & $objWMI_Variable.ConfigManagerErrorCode & "|" & "[uint32]" & @CRLF & _ "ConfigManagerUserConfig" & "|" & $objWMI_Variable.ConfigManagerUserConfig & "|" & "[boolean]" & @CRLF & _ "CreationClassName" & "|" & $objWMI_Variable.CreationClassName & "|" & "[string]" & @CRLF & _ "CurrentReading" & "|" & $objWMI_Variable.CurrentReading & "|" & "[sint32]" & @CRLF & _ "Description" & "|" & $objWMI_Variable.Description & "|" & "[string]" & @CRLF & _ "DeviceID" & "|" & $objWMI_Variable.DeviceID & "|" & "[string]" & @CRLF & _ "ErrorCleared" & "|" & $objWMI_Variable.ErrorCleared & "|" & "[boolean]" & @CRLF & _ "ErrorDescription" & "|" & $objWMI_Variable.ErrorDescription & "|" & "[string]" & @CRLF & _ "InstallDate" & "|" & $objWMI_Variable.InstallDate & "|" & "[datetime]" & @CRLF & _ "IsLinear" & "|" & $objWMI_Variable.IsLinear & "|" & "[boolean]" & @CRLF & _ "LastErrorCode" & "|" & $objWMI_Variable.LastErrorCode & "|" & "[uint32]" & @CRLF & _ "LowerThresholdCritical" & "|" & $objWMI_Variable.LowerThresholdCritical & "|" & "[sint32]" & @CRLF & _ "LowerThresholdFatal" & "|" & $objWMI_Variable.LowerThresholdFatal & "|" & "[sint32]" & @CRLF & _ "LowerThresholdNonCritical" & "|" & $objWMI_Variable.LowerThresholdNonCritical & "|" & "[sint32]" & @CRLF & _ "MaxReadable" & "|" & $objWMI_Variable.MaxReadable & "|" & "[sint32]" & @CRLF & _ "MinReadable" & "|" & $objWMI_Variable.MinReadable & "|" & "[sint32]" & @CRLF & _ "Name" & "|" & $objWMI_Variable.Name & "|" & "[string]" & @CRLF & _ "NominalReading" & "|" & $objWMI_Variable.NominalReading & "|" & "[sint32]" & @CRLF & _ "NormalMax" & "|" & $objWMI_Variable.NormalMax & "|" & "[sint32]" & @CRLF & _ "NormalMin" & "|" & $objWMI_Variable.NormalMin & "|" & "[sint32]" & @CRLF & _ "PNPDeviceID" & "|" & $objWMI_Variable.PNPDeviceID & "|" & "[string]" & @CRLF & _ "PowerManagementCapabilities" & "|" & $objWMI_Variable.PowerManagementCapabilities & "|" & "[uint16]" & @CRLF & _ "PowerManagementSupported" & "|" & $objWMI_Variable.PowerManagementSupported & "|" & "[boolean]" & @CRLF & _ "Resolution" & "|" & $objWMI_Variable.Resolution & "|" & "[uint32]" & @CRLF & _ "Status" & "|" & $objWMI_Variable.Status & "|" & "[string]" & @CRLF & _ "StatusInfo" & "|" & $objWMI_Variable.StatusInfo & "|" & "[uint16]" & @CRLF & _ "SystemCreationClassName" & "|" & $objWMI_Variable.SystemCreationClassName & "|" & "[string]" & @CRLF & _ "SystemName" & "|" & $objWMI_Variable.SystemName & "|" & "[string]" & @CRLF & _ "Tolerance" & "|" & $objWMI_Variable.Tolerance & "|" & "[sint32]" & @CRLF & _ "UpperThresholdCritical" & "|" & $objWMI_Variable.UpperThresholdCritical & "|" & "[sint32]" & @CRLF & _ "UpperThresholdFatal" & "|" & $objWMI_Variable.UpperThresholdFatal & "|" & "[sint32]" & @CRLF & _ "UpperThresholdNonCritical" & "|" & $objWMI_Variable.UpperThresholdNonCritical & "|" & "[sint32]" $i+=1 Next ConsoleWrite($strWMI_QueryResult & @CRLF) _ArrayAdd($arrWin32_TemperatureProbe, $strWMI_QueryResult) ; I'll wait for an answer... See you later :) If @error Then __ConsoleWrite("Error inserting item #" & $i & " in the $arrWin32_TemperatureProbe array.", @error, 9999) Else If $blnCustomArrayDisplay Then _ArrayDisplay($arrWin32_TemperatureProbe, "Win32_TemperatureProbe:", "", 64 + 32 + 4, "|", "VARIABLE NAME|ACTUAL VALUE|ENGINEERING UNIT", 350, 0xD3D3D3) If @error Then __ConsoleWrite("Error displaying the $arrWin32_TemperatureProbe array.", @error, 9999) EndIf EndIf If $blnReturnEU = False Then _ArrayColDelete($arrWin32_TemperatureProbe, 2) If @error Then __ConsoleWrite("Error deleting the column #2 of $arrWin32_TemperatureProbe array.") EndIf EndIf If IsArray($arrWin32_TemperatureProbe) Then Return $arrWin32_TemperatureProbe Else Return False EndIf EndIf EndIf EndIf EndIf EndFunc And I get this error ( undocumented in the Help File on _ArrayAdd() function ):
      [15/09/2017 10:24:46] : Error inserting item #4 in the $arrWin32_TemperatureProbe array. > Error: 0 Adding a ConsoleWrite() before the _ArrayAdd() function, I can see the content of $strWMI_QueryResult, and, here it is:
      QUERY RESULT|# 0|/
      Accuracy|32768|[sint32]
      Availability||[uint16]
      Caption|Sensore numerico|[string]
      ConfigManagerErrorCode||[uint32]
      ConfigManagerUserConfig||[boolean]
      CreationClassName|Win32_TemperatureProbe|[string]
      CurrentReading||[sint32]
      Description|CPU Thermal Probe|[string]
      DeviceID|root\cimv2 0|[string]
      ErrorCleared||[boolean]
      ErrorDescription||[string]
      InstallDate||[datetime]
      IsLinear||[boolean]
      LastErrorCode||[uint32]
      LowerThresholdCritical||[sint32]
      LowerThresholdFatal||[sint32]
      LowerThresholdNonCritical||[sint32]
      MaxReadable|1270|[sint32]
      MinReadable|64266|[sint32]
      Name|Sensore numerico|[string]
      NominalReading||[sint32]
      NormalMax||[sint32]
      NormalMin||[sint32]
      PNPDeviceID||[string]
      PowerManagementCapabilities||[uint16]
      PowerManagementSupported||[boolean]
      Resolution|1000|[uint32]
      Status|Unknown|[string]
      StatusInfo||[uint16]
      SystemCreationClassName|Win32_ComputerSystem|[string]
      SystemName|DESKTOP-25LFPVU|[string]
      Tolerance|32768|[sint32]
      UpperThresholdCritical||[sint32]
      UpperThresholdFatal||[sint32]
      UpperThresholdNonCritical||[sint32]QUERY RESULT|# 1|/
      Accuracy|32768|[sint32]
      Availability||[uint16]
      Caption|Sensore numerico|[string]
      ConfigManagerErrorCode||[uint32]
      ConfigManagerUserConfig||[boolean]
      CreationClassName|Win32_TemperatureProbe|[string]
      CurrentReading||[sint32]
      Description|True Ambient Thermal Probe|[string]
      DeviceID|root\cimv2 1|[string]
      ErrorCleared||[boolean]
      ErrorDescription||[string]
      InstallDate||[datetime]
      IsLinear||[boolean]
      LastErrorCode||[uint32]
      LowerThresholdCritical||[sint32]
      LowerThresholdFatal||[sint32]
      LowerThresholdNonCritical||[sint32]
      MaxReadable|1270|[sint32]
      MinReadable|64266|[sint32]
      Name|Sensore numerico|[string]
      NominalReading||[sint32]
      NormalMax||[sint32]
      NormalMin||[sint32]
      PNPDeviceID||[string]
      PowerManagementCapabilities||[uint16]
      PowerManagementSupported||[boolean]
      Resolution|1000|[uint32]
      Status|Unknown|[string]
      StatusInfo||[uint16]
      SystemCreationClassName|Win32_ComputerSystem|[string]
      SystemName|DESKTOP-25LFPVU|[string]
      Tolerance|32768|[sint32]
      UpperThresholdCritical||[sint32]
      UpperThresholdFatal||[sint32]
      UpperThresholdNonCritical||[sint32]QUERY RESULT|# 2|/
      Accuracy|32768|[sint32]
      Availability||[uint16]
      Caption|Sensore numerico|[string]
      ConfigManagerErrorCode||[uint32]
      ConfigManagerUserConfig||[boolean]
      CreationClassName|Win32_TemperatureProbe|[string]
      CurrentReading||[sint32]
      Description|Memory Module Thermal Probe|[string]
      DeviceID|root\cimv2 2|[string]
      ErrorCleared||[boolean]
      ErrorDescription||[string]
      InstallDate||[datetime]
      IsLinear||[boolean]
      LastErrorCode||[uint32]
      LowerThresholdCritical||[sint32]
      LowerThresholdFatal||[sint32]
      LowerThresholdNonCritical||[sint32]
      MaxReadable|1270|[sint32]
      MinReadable|64266|[sint32]
      Name|Sensore numerico|[string]
      NominalReading||[sint32]
      NormalMax||[sint32]
      NormalMin||[sint32]
      PNPDeviceID||[string]
      PowerManagementCapabilities||[uint16]
      PowerManagementSupported||[boolean]
      Resolution|1000|[uint32]
      Status|Unknown|[string]
      StatusInfo||[uint16]
      SystemCreationClassName|Win32_ComputerSystem|[string]
      SystemName|DESKTOP-25LFPVU|[string]
      Tolerance|32768|[sint32]
      UpperThresholdCritical||[sint32]
      UpperThresholdFatal||[sint32]
      UpperThresholdNonCritical||[sint32]QUERY RESULT|# 3|/
      Accuracy|32768|[sint32]
      Availability||[uint16]
      Caption|Sensore numerico|[string]
      ConfigManagerErrorCode||[uint32]
      ConfigManagerUserConfig||[boolean]
      CreationClassName|Win32_TemperatureProbe|[string]
      CurrentReading||[sint32]
      Description|Video Card Thermal Probe|[string]
      DeviceID|root\cimv2 3|[string]
      ErrorCleared||[boolean]
      ErrorDescription||[string]
      InstallDate||[datetime]
      IsLinear||[boolean]
      LastErrorCode||[uint32]
      LowerThresholdCritical||[sint32]
      LowerThresholdFatal||[sint32]
      LowerThresholdNonCritical||[sint32]
      MaxReadable|1270|[sint32]
      MinReadable|64266|[sint32]
      Name|Sensore numerico|[string]
      NominalReading||[sint32]
      NormalMax||[sint32]
      NormalMin||[sint32]
      PNPDeviceID||[string]
      PowerManagementCapabilities||[uint16]
      PowerManagementSupported||[boolean]
      Resolution|1000|[uint32]
      Status|Unknown|[string]
      StatusInfo||[uint16]
      SystemCreationClassName|Win32_ComputerSystem|[string]
      SystemName|DESKTOP-25LFPVU|[string]
      Tolerance|32768|[sint32]
      UpperThresholdCritical||[sint32]
      UpperThresholdFatal||[sint32]
      UpperThresholdNonCritical||[sint32]
       
      Could please anyone help me out? 
      Thanks in advance
      Francesco
    • TheDcoder
      By TheDcoder
      Hello, I recently opened a bug report without reading the Helpfile... My bad . After @Melba23's gentle reminder, I was curious about why it was like that.
      It is about SetError's behaviour. This is the example from the bug report:
      Example() If @error Then ConsoleWrite("Error" & @CRLF) Else ConsoleWrite("No Error" & @CRLF) EndIf Func Example() SetError(1) Sleep(1000) EndFunc What I tried to do is set Example's (my user defined function's) @error value to 1... but the value set by SetError is cleared after calling a function, I wonder why? Why should calling to an external function effect my function's @error which is set when my function returns.
      Setting the error of a UDF in advance by using SetError makes sense... but I cannot find a reason why calling a function should clear it? Please note that I am not talking about @error, I am talking about the @error set by my function when it ends/returns!
      I hope someone can enlighten me, thanks for the answers in advance!
      P.S I tried to explain my best but my English is not very good and I didn't feel like I did a good job explaining today, so please pardon any mistakes that I have made
    • water
      By water
      GreenCan and I are proud to present our Excel Chart UDF.

      The UDF is written for and tested with Microsoft Excel 2007 and Excel 2010. The example scripts won't run with older versions whereas the functions don't check the Excel version - you use them at your own risk.

      The UDF consists of one function to create a simple chart (you just have to specify the data area, the type of graph to draw, title, position of the chart etc.) and a lot of functions to create/modify all aspects of the chart.
      _XLChart_3D_Position........Set the 3D position of the chart (rotation, elevation, perspective) _XLChart_AreaGroupSet.......Set properties of an area chart group _XLChart_AxisSet............Set the properties of the selected axis (minimum, maximum value ...) _XLChart_BarGroupSet........Set properties of a bar chart group _XLChart_BubbleGroupSet.....Set properties of a bubble chart group _XLChart_ChartCreate........Create a chart in Excel on the specified worksheet or on a separate chart sheet _XLChart_ChartDataSet.......Sets all data related properties of an existing chart or chartsheet _XLChart_ChartDelete........Deletes a chart or chart sheet _XLChart_ChartExport........Exports the chart in a graphic format (GIF, JPG, PNG ...) or as PDF/XPS _XLChart_ChartPositionSet...Resize and reposition a chart object _XLChart_ChartPrint.........Print a chart or a chart sheet _XLChart_ChartSet...........Set properties for a chart _XLChart_ChartsGet..........Enumerate charts and chart sheets in a workbook _XLChart_ColumnGroupSet.....Set properties of a column chart group _XLChart_DatalabelSet.......Set properties for the data labels of a data series _XLChart_DoughnutGroupSet...Set properties of a doughnut chart group _XLChart_ErrorBarSet........Add or set properties of error bars for a data series _XLChart_FillSet............Set fill properties for the specified object (color, gradient, transparency ...) _XLChart_FontSet............Set font properties for the specified object (font name, size, bold, italic ...) _XLChart_GridSet............Set gridlines of a chart _XLChart_LayoutSet..........Set layout, style or template for a chart _XLChart_LegendSet..........Set properties of the legend (position, frame, shadow ...) _XLChart_LineGet............Returns properties of a line (axis line, grid line, data line ...) _XLChart_LineGroupSet.......Set properties of a line chart group _XLChart_LineSet............Set properties of a line (axis line, grid line, data line ...) like weight, color, style ... _XLChart_MarkerSet..........Set properties for the marker objects of line, scatter or radar charts _XLChart_ObjectDelete.......Delete an object from a chart _XLChart_ObjectPositionSet..Resize and reposition an object (plot area, legend ...) on a chart _XLChart_OfPieGroupSet......Set properties of a pie of pie or bar of pie chart group _XLChart_PageSet............Set the page setup attributes (paper size, orientation, margins etc.) for a chart or chart sheet _XLChart_PieGroupSet........Set properties of a pie or 3D-pie chart group _XLChart_ScreenUpdateSet....Turning screen updating on/off to improve performance _XLChart_SeriesAdd..........Add a data series to a chart _XLChart_SeriesSet..........Set properties of a data series _XLChart_ShadowSet..........Set properties of a shadow _XLChart_TicksSet...........Set tick marks and tick labels of a chart _XLChart_TitleGet...........Return information about a title. This can be the chart or any axis title _XLChart_TitleSet...........Set properties of a title. This can be the chart or any axis title _XLChart_TrendlineSet.......Add a new trendline or set properties of an existing trendline of a data series _XLChart_VersionInfo........Returns an array of information about the ExcelChart UDF For every function there is an example script available that shows what can be done with the respective function.
      There is a function cross reference available that documents which object (axis, dataline, legend ...) you can pass to which function.



      Play with the UDF and tell us what you like, what you would like to see improved or what is missing.
      Have fun!
      Download:
       
    • pboom
      By pboom
      I am looking for a way to retrieve filtered messages from the ‘system debug channel.' also known as  ‘kernel-mode debug output.'

      AutoIt must do the capture in real time. The following AutoIt UDF almost does what is required but it only captures application level, or Win32 debug output.

      https://www.autoitscript.com/forum/topic/82889-capture-debug-information-udf/#comment-593268

      The utility DebugView by Sysinternals captures the information as required by turning on Capture Kernal and in my case using the Filter include:

      *Incoming connection*

      The use of DebugView to do this is covered in the following tech note;

      https://www.tacticalsoftware.com/support/tech-notes/logging-com-port-activity.htm

      https://technet.microsoft.com/en-us/sysinternals/debugview.aspx

      However to make to make the information from DebugView available to my AutoIt script required DebugView capture to a text file and then my AutoIt script monitor that file for changes. The use of DebugView to capture the system debug channel could be made to work, but it was less than reliable and difficult to get started. The startup wasn’t something that could be easily automated not even with AutoIt.

      If you understood what I am talking about and made it this far, I think an explanation of the application is in order. Lots of details here sorry trying to answer questions in advance.

      I support a large installation of General Electric MUSE application. MUSE is a Windows-based medical application that processes and archives ECGs (electrocardiograms) taken on dedicated hardware (ECG Carts). Several methods exist on the cart to get the ECG from the Carts to the MUSE system; they range from floppies (on old obsolete hardware), memory cards, RS232 serial ports, and hardwired network connections.

      In our installation, we choose not to use the vendor-supplied network solution due to a variety of reasons I won’t get into here.  Instead, we have designed our own connection solution.

      We use a wireless serial server mounted on the ECG carts connecting to a server running a Serial/IP COM Port Redirector. The ECG cart and MUSE application think they are talking to each other via an RS232 port and as far as they are concerned, they are. However, this RS232 cable happens to run through our province (think State) wide Health Care WAN.  The hardware and software used can be seen on these two sites;

      http://www.bb-elec.com/Products/Wireless-Cellular/AirborneM2M-802-11-a-b-g-n-Dual-Band-Wireless/AirborneM2M-Industrial-Dual-Band-Wi-Fi-Router-Brid.aspx

      https://www.tacticalsoftware.com/virtual-serial-port-redirector/serial-ip.htm

      This setup works well we have over 130 ECG carts connecting using this setup. However, the end users are not technical, and there is a lot that can go wrong with wireless connections. So we do get complaints, often after the fact, that the ECG cart would not connect. A log of what ECG carts connected and when would be very helpful.

      The Serial redirector software can be configured to log all activity to the Kernal-mode Debug output. The serial redirector software itself being kernel level software. For configuration of the Wireless modules, we have custom written software (written in AutoIt) that amongst other things can display relevant configuration information for a Wireless module given it’s IP address.

      By extracting messages like the ones below from the Kernal-Mode Debug channel;

      COM56 : ½ Incoming connection from 10.158.188.172:51562

      COM18 : ½ Incoming connection from 10.158.188.200:50896

      COM19 : ½ Incoming connection from 10.158.188.180:59074

      COM68 : ½ Incoming connection from 142.239.15.82:34322

      We can have the module configuration program retrieve the configuration. The retrieved configuration contains more information such as the module ID number and wireless signal strength. This information is then logged to a file which is later loaded into a database. We can then query the database for connections made by a particular module within a specified time frame. The results of these Queries help us determine if the module was connected or is having problems connecting. Problems are usually indicated by poor signal strength and frequent re-connecting.

      So what I am looking for is a way for our module configuration program (written in AutoIt) to retrieve filtered Kernal-Level debug messages directly without using the DebugView application.

      The Forum post listed at the first of this message includes the source code for the DLL. So if you are versed in these matters and Visual Studio this may be an easy task. I looked at what needed to be done but, I was way over my head. If you look up the price of the serial IP redirector software, you can see that there is some money in our project for such things however, I do have a spending limit for purchases such as this.

       
    • jollypk
      By jollypk
      hI,
      Is there any UDF for Zeromq ?
      I need to communicate with a remote and local socket. Any suggestion or guidance will be very appreciated.
       
      Thanks