[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

OLE Object - combo box problem

Bu Mihai

9/29/2007 7:17:00 AM

-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
--
Posted via http://www.ruby-....
10 Answers

Masaki Suketa

9/29/2007 1:32:00 PM

0

Bulhac Mihai 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 Suketa

Masaki Suketa

9/29/2007 2:39:00 PM

0

Hello,

Bulhac Mihai 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

Bu Mihai

9/29/2007 5:19:00 PM

0

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?
--
Posted via http://www.ruby-....

Masaki Suketa

9/29/2007 10:11:00 PM

0

Bulhac Mihai 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")

Bu Mihai

9/30/2007 7:20:00 AM

0

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")
--
Posted via http://www.ruby-....

Bu Mihai

9/30/2007 7:31:00 AM

0

Bulhac Mihai 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:in `excel_asitt_menu'

207 line is :
modu=$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu)
--
Posted via http://www.ruby-....

Masaki Suketa

9/30/2007 8:48:00 AM

0

Hello,
Bulhac Mihai 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:in `excel_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 Suketa

Bu Mihai

9/30/2007 9:16:00 AM

0

Masaki Suketa wrote:
> Hello,
> Bulhac Mihai 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:in `excel_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 Suketa


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:in `excel_asitt_menu'
from excel2.rb:352

#line 208:
modu =
$asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu.name)
--
Posted via http://www.ruby-....

Masaki Suketa

9/30/2007 10:19:00 AM

0

Bulhac Mihai 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:in `excel_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 Suketa

Bu Mihai

9/30/2007 10:27:00 AM

0


TNX a lot man, its working now; indeed it was with that comp.name a
problem, tnx again
--
Posted via http://www.ruby-....