excel

[fbollaert]

Hello,

I have an excel file which uses the functions:
DEC2HEX
HEX2DEC

These functions can be enabled when activating 2 add-ins:
- analysis tool pack
- analysis tool pack vba

When I open the excel file from ruby I notice that excel is not running
the functions. The cells using the functions show: #NAME?

"text" message on these cells returns: #NAME?
and "value"returns some large integer.

Thank you for any help

 

[greg.rb]

Make sure it works when you run Excel. If the addins are properly
installed you are good to go. (Tools/Add-ins...)

require 'win32ole'

xlApp=WIN32OLE::new('Excel.Application')
xlApp.Visible=1
xl_file = ('c:\\Book2.xls')
xlApp.Workbooks.Open(xl_file)


puts xlApp.Cells(1,1).Formula
puts xlApp.Cells(1,1).Value

xlApp.ActiveWorkbook.Close()
xlApp.Quit

Produces:

 

[bbiker]

I believe that the OP meant that occurred when the ruby program created
a new workbook.
For some reason unknown to me, win32ole does not open the Excel
application with the addins. When I open normally as a user, the addins
are automatically loaded (if they were previously added)

This is illustrated with the following Ruby script.

When run with open_book set to false
cell(A2) contains #NAME?
cell(A1) contains 255 ... 0XFF was automatically converted to
255
=HEX2DEC(A1) appears in the formula window when cell
A2 is selected

__START__CODE__

#!c:\ruby\bin\ruby
# -*- coding: ISO-8859-1 -*-

require 'win32ole'

xlApp = WIN32OLE::new('Excel.Application')
xlApp.Visible = 1

open_book = true

if open_book
xl_file = ('c:\\Book2.xls')
xlApp.Workbooks.Open(xl_file)
else
workbook = xlApp.Workbooks.Add();
sheet = workbook.Worksheets(1);

xlApp.Cells(1,1).Value = 0XFF
xlApp.Cells(1,2).Formula = '=HEX2DEC(A1)'
end

puts "#{xlApp.Cells(1, 1).Value}"
puts "#{xlApp.Cells(1, 2).Formula}"
puts "#{xlApp.Cells(1, 2).Value}"

# Wait for user input...
print "Press <return> to continue..."
gets

xlApp.ActiveWorkbook.Close()
xlApp.Quit

__END__CODE__

Output of run1 with open_book set to true:
C:\Documents and Settings\..\win32ole>excel_hex2dec.rb
FF
=HEX2DEC(A1)
255.0
Press <return> to continue...

Output of run 2 with open_book set to false:
C:\Documents and Settings\..\win32ole>excel_hex2dec.rb
255.0
=HEX2DEC(A1)
-2146826259
Press <return> to continue...

 

[fbollaert]

The problem was indeed that the function (HEX2DEC) does not run when
excel is started from ruby. (even though the addin was installed)

However the problem only occurred when opening an excel that was saved
as a html document. You can save a workbook as a collection of html
files, without loosing the excel functionality.

When I saved the html document as an excel workbook again, the problem
disappeared. So this is a work around for the problem.

Thank you

 

[greg.rb]

I tried:

xlApp.AddIns("Analysis ToolPak").Installed = true
xlApp.AddIns("Analysis ToolPak - VBA").Installed = true

but it didn't help. somehow we need to force the addin to actually
load when creating the com object.

later...until we find the answer...

 

[bbiker]

I tried:

xlApp.AddIns("Analysis ToolPak").Installed = true
xlApp.AddIns("Analysis ToolPak - VBA").Installed = true

but it didn't help. somehow we need to force the addin to actually
load when creating the com object.

later...until we find the answer...

It appears that you first must uninstall and then re-install the
addin!!!

Refer to the code below:
First run with open_book = 0

when prompted to navigate to where you want to store the workbook
....should be as specified in xl_file

then run with open_book = 1

in both cases the script runs as expected!!!!

__CODE__
require 'win32ole'

xlApp = WIN32OLE::new('Excel.Application')
xlApp.Visible = 1

puts "Analysis ToolPark installed? " +
"#{xlApp.AddIns("Analysis ToolPak").Installed}"
puts "Analysis ToolPak - VBA installed? " +
"#{xlApp.AddIns("Analysis ToolPak - VBA").Installed}"

# let's try to uninstall and re-install analysis addin
# not sure if the VBA is required ...
# probably only if you do VBA excel macros
# needs further investigation

xlApp.AddIns("Analysis ToolPak").Installed = 0
xlApp.AddIns("Analysis ToolPak - VBA").Installed = 0

xlApp.AddIns("Analysis ToolPak").Installed = 1
xlApp.AddIns("Analysis ToolPak - VBA").Installed = 1


open_book = 1
xl_path = Dir.pwd

if open_book == 1
xl_file = xl_path + '/Book1.xls'
xlApp.Workbooks.Open(xl_file)
# sheet1 = xlApp.Workbooks.Worksheets(1);

puts "Cell(A1) value: #{xlApp.Cells(1, 1).Value}"
puts "Cell(A2) formula: #{xlApp.Cells(1, 2).Formula}"
puts "Cell(A2) value: #{xlApp.Cells(1, 2).Value}"

# lets change the worksheet
xlApp.Cells(1,3).Formula = '=DEC2HEX(B1,4)'
puts "Cell(A3) formula: #{xlApp.Cells(1, 3).Formula}"
puts "Cell(A3) value: #{xlApp.Cells(1, 3).Value}"

else
workbook = xlApp.Workbooks.Add();
sheet1 = workbook.Worksheets(1);

sheet1.Range("A1").NumberFormat = "@"
sheet1.Range("B1").NumberFormat = "0"
sheet1.Range("C1").NumberFormat = "@"

sheet1.Cells(1,1).Value = 'FF'
sheet1.Cells(1,2).Formula = '=HEX2DEC(A1)'

puts "#{sheet1.Cells(1, 1).Value}"
puts "#{sheet1.Cells(1, 2).Formula}"
puts "#{sheet1.Cells(1, 2).Value}"
end


# Wait for user input...
print "Press <return> to continue..."
gets

xlApp.ActiveWorkbook.Close()
xlApp.Quit

__END__CODE__

 

[bbiker]

but it didn't help. somehow we need to force the addin to actually

It appears that you first must uninstall and then re-install the
addin!!!

Refer to the code below:
First run with open_book = 0

when prompted to navigate to where you want to store the workbook
...should be as specified in xl_file

then run with open_book = 1

in both cases the script runs as expected!!!!

PS I tried this on an excel workbook saved as an htm file. From what I
can tell it works as expected. No need to open the htm file and save it
as xls file.

 

[greg.rb]

It appears that you first must uninstall and then re-install the
addin!!!

Glad you found a solution. Any idea why it behaves this way?
-Greg

 

[bbiker]

Glad you found a solution. Any idea why it behaves this way?
-Greg

I found this in the description of Installed properly description in
the online Excel Visual Basic documentation.

----- Setting this property to True installs the add-in and calls its
Auto_Add functions. Setting this property to False removes the add-in
and calls its Auto_Remove functions. ---

So my guess is as follows:
If when you set Installed to 1 (True) and Installed is already set to 1
then the Auto_Add functions are not invoked. Likewise setting Installed
to 0 (False) behaves in a similar manner..

you cannot uninstall something that has not been installed.
no point in installing something if it is already installed.

It seems that when Excel is started "normally" the re-installation of
addins is automatic.
Obviously, this does not occur when Excel is invoked via WIN32OLE.