Running Excel Macro from Ruby

[imaband]

Hi,

I have a problem which involves calling an excel macro from another application – in this case, ruby.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o><o></o>
$excel.Run "ActionCommands.xlsm!CreateDynamicDataSources"
<o></o><o></o>
Here is the simplified macro that it calls
<o></o>
Sub CreateDynamicDataSources()<o></o>
<o></o>
MsgBox "Hello"<o></o>
ActiveCell.Select<o></o>
ActiveCell.Formula = "Hello"<o></o>
<o></o>
End Sub<o></o>
<o></o>
<o></o>
The good news is, the macro is indeed being called because the MsgBox is executing. It may even be able to read cells and send the data back to ruby or display it in a message box. The bad news is I can’t select a sheet or cell and actually write to it! When I call this macro from Excel it works as expected. How do I fool Excel (2007) into thinking it’s OK to allow these operations. There is no explicit message that says “Hey this is in read only mode” but I suspect that is what is going on. Does anyone know of a work-around, solution, or better way of doing this?
<o></o>
Jeff

 

[Guillermo]

It also works like this:

import win32com.client

xls = win32com.client.Dispatch("Excel.Application")
xls.visible = True
xls.Run(r"'c:\guillermo\dev\tmp\test.xlsm'!DoStuff", "Hello from Python!")
xls.ActiveCell.EntireColumn.AutoFit()