-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
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?
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")
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)
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.
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.
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)
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')