# How to get value of constants in Excel using WIN32OLE

**URL:** <https://www.ruby-forum.com/t/how-to-get-value-of-constants-in-excel-using-win32ole/77874>\
**Category:** Ruby\
**Created:** [December 1, 2006, 5:04am UTC](https://www.ruby-forum.com/t/how-to-get-value-of-constants-in-excel-using-win32ole/77874 "2006-12-01T05:04:27Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\
**Post date:** [December 1, 2006, 5:04am UTC](https://www.ruby-forum.com/t/how-to-get-value-of-constants-in-excel-using-win32ole/77874/1 "2006-12-01T05:04:27Z")

</div>

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

> Exit code: 0

---

<div class="post-metadata">

**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\
**Post date:** [December 1, 2006, 9:35am UTC](https://www.ruby-forum.com/t/how-to-get-value-of-constants-in-excel-using-win32ole/77874/2 "2006-12-01T09:35:01Z")

</div>

On 12/1/06, Li Chen [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> which is so inefficient!
> 
> WIN32OLE.const\_load(excel, Excel\_Const)  
> end  
> “Excel\_Const::Xl3DArea”
> 
> > Exit code: 0  
> > See programming ruby, especially the chapter on reflection. THe  
> > interesting methods are: Module#constants, Module#const\_get

---

<div class="post-metadata">

**Author:** ![alex-osu3](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/13edae/32.png) [@alex-osu3](https://www.ruby-forum.com/u/alex-osu3)\
**Post date:** [December 1, 2006, 3:43pm UTC](https://www.ruby-forum.com/t/how-to-get-value-of-constants-in-excel-using-win32ole/77874/3 "2006-12-01T15:43:21Z")

</div>

Jan S. wrote:

> On 12/1/06, Li Chen [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > which is so inefficient!
> > 
> > WIN32OLE.const\_load(excel, Excel\_Const)  
> > end  
> > “Excel\_Const::Xl3DArea”
> > 
> > > Exit code: 0  
> > > 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