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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

imaband

New Member
Joined
Dec 16, 2008
Messages
3
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
 

imaband

New Member
Joined
Dec 16, 2008
Messages
3

ADVERTISEMENT

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.
 

Guillermo

New Member
Joined
Sep 22, 2005
Messages
19
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
 

Guillermo

New Member
Joined
Sep 22, 2005
Messages
19

ADVERTISEMENT

And it's in Word, sorry! :p
 

Guillermo

New Member
Joined
Sep 22, 2005
Messages
19
Oops, sorry. You already knew all this. I think I don't get what's your problem then...
 

gauntletxg

Well-known Member
Joined
Jul 15, 2008
Messages
636
I'm not familiar with Ruby at all, but have you tried something like

Code:
$excel.Workbooks.Open "C:\File.xls"
$excel.Run "MacroName"
 

Guillermo

New Member
Joined
Sep 22, 2005
Messages
19
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,861
Messages
5,766,801
Members
425,379
Latest member
thedoctor00

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
Top