Bernard Kenik
7/8/2006 7:28:00 PM
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__