How to get value of constants in Excel using WIN32OLE

[Li Chen]

Hi all,

I have a script to store all Excel constants in an array. ButI can't
return the value for each constant. By using "inspect" method I think
all the constants in the array are turn into a string. The only chance I
can get the value of a constant is that I run the script to print out
all the constants and then add a line outside the array

puts Excel_Const::XlYDMFormat => return a value of 8

which is so inefficient!

I also notice that the line below will return a string

puts "Excel_Const::XlYDMFormat" => return a string as
Excel_Const::XlYDMFormat

I want to print out the values for all the constants at once. I wonder
if any expert out there can give me a hand.

Thank you in advnace,

Li

######
equire 'win32ole'
require 'enumerator'

module Excel_Const
end

excel=WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, Excel_Const)

array=Array.new
Excel_Const.constants.each{|c| array<<c}

count=0
array.sort!.each_slice(5) do |slice|
slice.each do |con|
v="Excel_Const::"+"#{con}"
puts v.inspect
count+=1
end
end

puts "The value is\t", Excel_Const::XlYDMFormat

puts "The total constants are ", count

##output

ruby consts.rb

"Excel_Const::CONSTANTS"
"Excel_Const::Xl24HourClock"
"Excel_Const::Xl3DArea"
"Excel_Const::Xl3DAreaStacked"
...
"Excel_Const::XlYDMFormat"
...

The value is
8
The total constants are
1387

 

[Jan Svitok]

Hi all,

I have a script to store all Excel constants in an array. ButI can't
return the value for each constant. By using "inspect" method I think
all the constants in the array are turn into a string. The only chance I
can get the value of a constant is that I run the script to print out
all the constants and then add a line outside the array

puts Excel_Const::XlYDMFormat => return a value of 8

which is so inefficient!

I also notice that the line below will return a string

puts "Excel_Const::XlYDMFormat" => return a string as
Excel_Const::XlYDMFormat

I want to print out the values for all the constants at once. I wonder
if any expert out there can give me a hand.

Thank you in advnace,

Li

######
equire 'win32ole'
require 'enumerator'

module Excel_Const
end

excel=WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, Excel_Const)

array=Array.new
Excel_Const.constants.each{|c| array<<c}

count=0
array.sort!.each_slice(5) do |slice|
slice.each do |con|
v="Excel_Const::"+"#{con}"
puts v.inspect
count+=1
end
end

puts "The value is\t", Excel_Const::XlYDMFormat

puts "The total constants are ", count

##output
"Excel_Const::CONSTANTS"
"Excel_Const::Xl24HourClock"
"Excel_Const::Xl3DArea"
"Excel_Const::Xl3DAreaStacked"
...
"Excel_Const::XlYDMFormat"
...

The value is
8
The total constants are
1387

See programming ruby, especially the chapter on reflection. THe
interesting methods are: Module#constants, Module#const_get

 

[Li Chen]

See programming ruby, especially the chapter on reflection. THe
interesting methods are: Module#constants, Module#const_get

Thanks Jan. I haven't touch this chapter yet but I will take a look at
them. BTW could you browse my last post about "VBA to Ruby code again"?
I change every constants into the required format and Ruby still
complains the script.


Li