Home |
Search |
Today's Posts |
#1
Posted to comp.lang.tcl,microsoft.public.excel.programming
|
|||
|
|||
Tcl's Tcom meets Office 2003 - giving pre-.NET Visual Basic for Applications a small taste of introspection the long way round...
The following Tcl code produces a large number of VBA routines for
displaying the names of various Excel enumeration constants, for use in (say) recovering the design of spreadsheets in a non-.NET environment. If you look at all the code generated, you may well just want to say why not use ToString (with a side order of System.Enum.Parse, say), but this may still prove useful for any customers who are using Excel 2003 who don't want to buy into that particular upgrade path at the moment. It certainly seems to have been something people have been looking for over the years, judging by remarks I've seen in various newsgroups, websites, and forums. I can easily believe that this will reproduce someone else's work. If so, sorry - didn't find your work anywhere. The fact that the same trick will work for other Microsoft object libraries may well help in other Office automation activities. Notes : (1) You may well need to alter the path to the EXCEL.EXE file here (double-check your path from your Tools/References). (2) The generated code is still possibly under-tested - let me know if you find anything odd or non-idiomatic. (3) You might need a Tcl interpreter with Tcom included if you don't already have one - http://tcl.tk or http://www.activestate.com should help you find what you need... package import tcom tcom::import {C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE} set vb [open excelenums_vb.txt w] puts $vb {' This module contains a large number of functions for converting ' Excel enumeration types into their string literals. ' If so desired, a similar set of conversion routines could be produced very rapidly. ' The code derives its data from the .COM model for version 11 of Excel, as found ' in Office 2003 Professional. ' Author: Brian Passingham} puts $vb "' Date [clock format [clock seconds] -format "%D"]\n\n\n" foreach tname [info vars ::Excel::*] { if {![regexp {__} $tname] && [regexp {::(\w+)$} $tname void typeName] && ![string equal $typeName Constants]} { puts $vb "Function EnumNameOf${typeName} (ByVal x As $ {typeName})" puts $vb { Dim res As String Select Case x} foreach {key value} [array get $tname] { puts $vb " Case $value" puts $vb " res = \"$key\"" } puts $vb { Case Else res = "Not found " & x End Select } puts $vb " EnumNameOf${typeName} = res\nEnd Function\n\n" } } puts $vb "' End of file" close $vb And here's a sample generated VB routine: Function EnumNameOfXlMarkerStyle(ByVal x As XlMarkerStyle) Dim res As String Select Case x Case 8 res = "xlMarkerStyleCircle" Case 5 res = "xlMarkerStyleStar" Case -4147 res = "xlMarkerStylePicture" Case 9 res = "xlMarkerStylePlus" Case 3 res = "xlMarkerStyleTriangle" Case -4105 res = "xlMarkerStyleAutomatic" Case -4142 res = "xlMarkerStyleNone" Case -4168 res = "xlMarkerStyleX" Case 1 res = "xlMarkerStyleSquare" Case 2 res = "xlMarkerStyleDiamond" Case -4118 res = "xlMarkerStyleDot" Case -4115 res = "xlMarkerStyleDash" Case Else res = "Not found " & x End Select EnumNameOfXlMarkerStyle = res End Function Hope this helps someone else. The last hour has certainly saved me a LOT of work. Regards, Brian Passingham, Passingham Software Ltd, http://www.passisoft.com |
#2
Posted to comp.lang.tcl,microsoft.public.excel.programming
|
|||
|
|||
Tcl's Tcom meets Office 2003 - giving pre-.NET Visual Basic for Applications a small taste of introspection the long way round...
On Feb 19, 7:58 pm, wrote:
Notes : (4) The code will need a bit of reformatting since gmail has (not very kindly) word-wrapped it. Grrr. Sorry. If anyone who needs it can't sort it out by themselves, let me know and I'll e-mail either it or the 4900 lines of VBA it produces. |
#3
Posted to comp.lang.tcl,microsoft.public.excel.programming
|
|||
|
|||
Tcl's Tcom meets Office 2003 - giving pre-.NET Visual Basic for Applications a small taste of introspection the long way round...
I've added the code and a few other comments to the Tcl wiki's page on
"How one discovers the API for a COM exporting application" at http://wiki.tcl.tk/4472 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Microsoft Visual Basic for Applications Extensibilty 5.3 | Excel Programming | |||
Visual Basic for Applications | Excel Programming | |||
Visual Basic For Applications | Excel Programming | |||
Microsoft Office 2003 issues with Visual Basic | Excel Programming |