OLE Object - combo box problem

Ruby
1

-i want to add to an excel worksheet a meniu accessing from a combo box
but i have problems insering data in the combo box
-in the vba i have combo.additem “opt1”
-in ruby i cant use additem item (unknown property or method `additem’);
what can i use instead?, i dont want to use fillinrange; and how can i
go to another worksheet after i selected a option in the combo box

i have : $combo_box=$worksheet_menu.OLEObjects.Add “Forms.ComboBox.1”
$combo_box[‘additem’]=“opt1” doesnt work
tnx

2

Bulhac M. wrotes:

i have : $combo_box=$worksheet_menu.OLEObjects.Add “Forms.ComboBox.1”
$combo_box[‘additem’]=“opt1” doesnt work
tnx

Try $combo_box.object.additem(“opt1”).

Regards,
Masaki S.

3

tnx a lot, now additem its working, but for the combo box menu make it
work (options to be links to another worksheet) i dont want tu use ruby,
i want that with ruby script to record a macro and that macro is called
when i select a option from the combo box because my ruby script is
closed when i’m reading the excel file; is that possible?

4

Hello,

Bulhac M. wrote:

what can i use instead?, i dont want to use fillinrange; and how can i
go to another worksheet after i selected a option in the combo box

Sorry, I had not noticed your second question.

Try to use WIN32OLE_EVENT and ‘MdcComboEvents’.

require ‘win32ole’
ex = WIN32OLE.new(‘Excel.Application’)
ex.visible = true
book = ex.workbooks.add
sheet = book.worksheets(1)
combo = sheet.OLEObjects.Add(“Forms.Combobox.1”).object

book.worksheets.each do |sheet|
combo.additem(sheet.name)
end
combo.additem(“exit ruby”)

ev = WIN32OLE_EVENT.new(combo, ‘MdcComboEvents’)
ev.on_event(‘Change’){|*arg|
if combo.text == ‘exit ruby’
$LOOP = false
else
book.worksheets(combo.text).activate
end
}
$LOOP = true
while $LOOP
WIN32OLE_EVENT.message_loop
sleep 0.1
end

5

Bulhac M. wrote:

tnx a lot, now additem its working, but for the combo box menu make it
work (options to be links to another worksheet) i dont want tu use ruby,
i want that with ruby script to record a macro and that macro is called
when i select a option from the combo box because my ruby script is
closed when i’m reading the excel file; is that possible?

I’m not sure that I understand what you want to do,
But does the following script help you?

require ‘win32ole’
ex = WIN32OLE.new(‘Excel.Application’)
ex.visible = true
book = ex.workbooks.add
sheet = book.worksheets(1)
combo = sheet.OLEObjects.Add(“Forms.Combobox.1”).object
oles = sheet.OLEObjects
book.worksheets.each do |sh|
combo.additem(sh.name)
end
modu = book.VBProject.VBComponents.Item(sheet.name)
modu.CodeModule.InsertLines(1, "Private Sub " + combo.name + “_Change”)
modu.CodeModule.InsertLines(2, " MsgBox " + combo.name + “.Text” )
modu.CodeModule.InsertLines(3, “End Sub”)

6

Yes, this is what i want, tnx a lot, but it gives me this error:

OLE error code:800A03EC in Microsoft Office Excel
Programmatic access to Visual Basic Project is not trusted

HRESULT error code:0x80020009
  Exception occurred.  from excel2.rb:205:in `excel_asitt_menu'

from excel2.rb:349

my code is:

modu =
$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu)
modu.CodeModule.InsertLines(1, "Private Sub " + $combo_box + “_Change”)
modu.CodeModule.InsertLines(2, " MsgBox " + $combo_box + “.Text” )
modu.CodeModule.InsertLines(3, “End Sub”)

7

Hello,
Bulhac M. wrote:

previous error was resolved, but i still get one:

excel2.rb:207:in method_missing': Item (WIN32OLERuntimeError) OLE error code:800A000D in VBAProject Type mismatch HRESULT error code:0x80020009 Exception occurred. from excel2.rb:207:inexcel_asitt_menu’

207 line is :

modu=$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu)

Is $worksheet_asitt_menu WIN32OLE object?
If so, try
Item($worksheet_asitt_menu.name)

Item’s argument must be Worksheet name string(ie. String object)
not WIN32OLE object.

Regards,
Masaki S.

8

Bulhac M. wrote:

Yes, this is what i want, tnx a lot, but it gives me this error:

OLE error code:800A03EC in Microsoft Office Excel
Programmatic access to Visual Basic Project is not trusted

HRESULT error code:0x80020009
  Exception occurred.  from excel2.rb:205:in `excel_asitt_menu'

from excel2.rb:349

my code is:

modu =
$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu)
modu.CodeModule.InsertLines(1, "Private Sub " + $combo_box + “_Change”)
modu.CodeModule.InsertLines(2, " MsgBox " + $combo_box + “.Text” )
modu.CodeModule.InsertLines(3, “End Sub”)

previous error was resolved, but i still get one:

excel2.rb:207:in method_missing': Item (WIN32OLERuntimeError) OLE error code:800A000D in VBAProject Type mismatch HRESULT error code:0x80020009 Exception occurred. from excel2.rb:207:inexcel_asitt_menu’

207 line is :
modu=$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu)

9

Masaki S. wrote:

Hello,
Bulhac M. wrote:

previous error was resolved, but i still get one:

excel2.rb:207:in method_missing': Item (WIN32OLERuntimeError) OLE error code:800A000D in VBAProject Type mismatch HRESULT error code:0x80020009 Exception occurred. from excel2.rb:207:inexcel_asitt_menu’

207 line is :

modu=$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu)

Is $worksheet_asitt_menu WIN32OLE object?
If so, try
Item($worksheet_asitt_menu.name)

Item’s argument must be Worksheet name string(ie. String object)
not WIN32OLE object.

Regards,
Masaki S.

i have this:

$excel = WIN32OLE.new(‘Excel.Application’)
$worksheet_asitt_menu = $asitt_workbook.Worksheets(1)
$worksheet_asitt_menu.name=“ASITT_MENU”

$combo_box=$worksheet_asitt_menu.OLEObjects.Add(“Forms.Combobox.1”).object

modu =
$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu.name)
modu.CodeModule.InsertLines(1, “Private Sub ComboBox1_Change”)
modu.CodeModule.InsertLines(2, " MsgBox “asdasd”" )
modu.CodeModule.InsertLines(3, “End Sub”)

-combobox1 is the name for combo box

with this code i still have that error:

excel2.rb:208:in method_missing': Item (WIN32OLERuntimeError) OLE error code:800A0009 in VBAProject Subscript out of range HRESULT error code:0x80020009 Exception occurred. from excel2.rb:208:inexcel_asitt_menu’
from excel2.rb:352

#line 208:
modu =
$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu.name)

10

TNX a lot man, its working now; indeed it was with that comp.name a
problem, tnx again

11

Bulhac M. wrote:

i have this:

$excel = WIN32OLE.new(‘Excel.Application’)
$worksheet_asitt_menu = $asitt_workbook.Worksheets(1)
$worksheet_asitt_menu.name=“ASITT_MENU”

$combo_box=$worksheet_asitt_menu.OLEObjects.Add(“Forms.Combobox.1”).object

modu =

$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu.name)

modu.CodeModule.InsertLines(1, “Private Sub ComboBox1_Change”)
modu.CodeModule.InsertLines(2, " MsgBox “asdasd”" )
modu.CodeModule.InsertLines(3, “End Sub”)

-combobox1 is the name for combo box

with this code i still have that error:

excel2.rb:208:in method_missing': Item (WIN32OLERuntimeError) OLE error code:800A0009 in VBAProject Subscript out of range HRESULT error code:0x80020009 Exception occurred. from excel2.rb:208:inexcel_asitt_menu’
from excel2.rb:352

#line 208:
modu =

$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu.name)

That’s because you have changed $worksheet_asitt_menu.name to
‘ASITT_MENU’,
and you don’t specify correct VBComponent name as Item argument.
(The error message is ‘Subscript out of range’.)

At first, check available name by using following code
before line 208.
$asitt_workbook.VBProject.VBComponents.each do |comp|
puts comp.name
end

Or try to invoke VBE of Excel and check available names.
If you could see ‘Sheet1(ASITT_MENU)’ in VBAProject, then
specify ‘Sheet1’ instead of $worksheet_asitt_menu.name

modu =
$asitt_workbook.VBProject.VBComponents.Item(‘Sheet1’)

Regards,
Masaki S.