Running Excel Macro from Ruby

imaband

New Member
Joined
Dec 16, 2008
Messages
3
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-com:eek:ffice:eek:ffice" /><o:p></o:p><o:p></o:p>
$excel.Run "ActionCommands.xlsm!CreateDynamicDataSources"
<o:p></o:p><o:p></o:p>
Here is the simplified macro that it calls
<o:p></o:p>
Sub CreateDynamicDataSources()<o:p></o:p>
<o:p></o:p>
MsgBox "Hello"<o:p></o:p>
ActiveCell.Select<o:p></o:p>
ActiveCell.Formula = "Hello"<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
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:p></o:p>
Jeff
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Jeff

I've no idea what ruby is, but I suggest you remove everything apart from this.
Rich (BB code):
ActiveCell.Formula = "Hello"
Displaying a message box will stop code execution and you shouldn't need to select the active cell, it's already selected.:)
 
Upvote 0
Norie,

I appreciate the prompt reply. Granted ActiveCell.Select is useless but the only reason the MsgBox call was there was to provide some evidence that the vba function was even getting called. MsgBox doesn't stop code execution, it merely pauses it until you retire the prompt window. I could put 100 MsgBox statements in my macro and it would execute all of them as long as I hit OK on each of them. If I interspersed 100 ActiveCell.Formula commands among them None of them would execute. So we have an Excel macro that runs some commands but not others and I'm back to where I started. Removing the useless code does not make the code I need work. Hopefully the solution is something simple like activate (which I already tried) or something.

Jeff
 
Upvote 0
Thanks for the tip Guillermo but we're pretty entrenched in ruby. Do you have an example of a call from Python to an VBA excel macro? If both Python and Ruby use WIN32OLE maybe I'm just missing a step or something.
 
Upvote 0
Depending on what you wanted to do, this might work for you. I think it's the cleanest solution, albeit a bit simplistic:

Put this in a .py file:

import win32com.client

wd = win32com.client.Dispatch("Word.Application")
wd.visible = 1

doc = wd.Documents.Open(FileName=r"d:\guillermo\dev\tmp\pymso\test.doc", Visible=True)
wd.Run("Module1.PrintMessage", "Hello from Python!")

Put this in a module in Word:

Public Sub PrintMessage(msg As String)
MsgBox msg
End Sub

The key is the Run method of the Application object. It lets you pass in the name of a VBA function you want to execute.

Cheers,

Guillermo
 
Upvote 0
I'm not familiar with Ruby at all, but have you tried something like

Code:
$excel.Workbooks.Open "C:\File.xls"
$excel.Run "MacroName"
 
Upvote 0
Second attempt.

This code goes in an Excel code module called Module1:

Code:
Sub DoStuff(msg As String)
    ActiveCell.Value = msg
    ActiveCell.Offset(0, 1).Formula = "=LEN(" & ActiveCell.Address & ")"
End Sub

This code goes in a .py file:

Code:
import win32com.client

xl = win32com.client.Dispatch("Excel.Application")
xl.visible = True

#Watch out for rogue spellings in Excel's params names (Filename)
xl.Workbooks.Open(Filename=r"c:\guillermo\dev\tmp\test.xlsm")
xl.Run("Module1.DoStuff", "Hello from Python!")
xl.ActiveCell.EntireColumn.AutoFit()

The program behaves as expected. I'm manipulating the ActiveCell from VBA as well as from python.

Is this what you're after?

Regards,

Guillermo
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top