B
brian.passingham
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
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 voidtypeName] && ![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