Excel

ruby-talk
1

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:

ruby Excel_hex2dec.rb

=HEX2DEC(255)
597.0

···

Exit code: 0

2

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...

3

greg.rb wrote:

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__

4

bbiker wrote:

> I tried:
>
> xlApp.AddIns("Analysis ToolPak").Installed = true
> xlApp.AddIns("Analysis ToolPak - VBA").Installed = true
>

greg.rb wrote:

> 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!!!!

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.

5

bbiker wrote:

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

6

greg.rb wrote:

bbiker wrote:

> 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

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.