# How to translate a VBA code into Ruby script

**URL:** <https://rubytalk.org/t/how-to-translate-a-vba-code-into-ruby-script/33086>\
**Category:** ruby-talk\
**Created:** [20 November 2006 13:14 UTC](https://rubytalk.org/t/how-to-translate-a-vba-code-into-ruby-script/33086 "2006-11-20T13:14:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Guest2](https://avatars.discourse-cdn.com/v4/letter/g/439d5e/32.png) [@Guest2](https://rubytalk.org/u/Guest2)\
**Post date:** [20 November 2006 13:14 UTC](https://rubytalk.org/t/how-to-translate-a-vba-code-into-ruby-script/33086/1 "2006-11-20T13:14:26Z")

</div>

Hi all,

I need to use WIN32OLE to do some Excel automation. I read some  
documents and search google.The examples of how to use WIN32OLE are  
almost the same. But none of them show how to translate a VBA code into  
Ruby. Here are the VBA codes:

Sub AddNew()  
Set NewBook = Workbooks.Add  
&nbsp;&nbsp;&nbsp;&nbsp;With NewBook  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Title = "All Sales"  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Subject = "Sales"  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.SaveAs Filename:="Allsales.xls"  
&nbsp;&nbsp;&nbsp;&nbsp;End With  
End Sub

And here are some Ruby codes:  
require 'win32ole'

excel=WIN32OLE.new('Excel.Application')  
workbook=excel.Workbooks.Add  
worksheet=workbook.Worksheets.Add

I wonder if any expert out there could give me an example on how to  
translate Title, Subject, and SaveAs Filename into to Ruby codes.

Thanks,

Li

> **···**
>
> --  
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).

---

<div class="post-metadata">

**Author:** ![Jan\_Svitok](https://avatars.discourse-cdn.com/v4/letter/j/cab0a1/32.png) [@Jan\_Svitok](https://rubytalk.org/u/Jan_Svitok)\
**Post date:** [20 November 2006 13:33 UTC](https://rubytalk.org/t/how-to-translate-a-vba-code-into-ruby-script/33086/2 "2006-11-20T13:33:20Z")

</div>

require 'win32ole'

excel=WIN32OLE.new('Excel.Application')  
#Set NewBook = Workbooks.Add  
newbook=excel.Workbooks.Add  
#With NewBook  
# .Title = "All Sales"  
newbook.Title = "All Sales"  
# .Subject = "Sales"  
newbook.Subject = "Sales"  
# .SaveAs Filename:="Allsales.xls"  
newbook.SaveAs Filename =\> "Allsales.xls"

or

newbook['Title'] = "All Sales"  
newbook['Subject'] = "Sales"  
newbook.SaveAs Filename =\> "Allsales.xls"

please read [Programming Ruby: The Pragmatic Programmer's Guide](http://ruby-doc.org/docs/ProgrammingRuby/html/win32.html)  
and [http://ruby-doc.org/stdlib/libdoc/win32ole/rdoc/index.html](http://ruby-doc.org/stdlib/libdoc/win32ole/rdoc/index.html)

> **···**
>
> On 11/20/06, Li Chen \<chen\_li3@yahoo.com\> wrote:
> 
> > Hi all,
> > 
> > I need to use WIN32OLE to do some Excel automation. I read some  
> > documents and search google.The examples of how to use WIN32OLE are  
> > almost the same. But none of them show how to translate a VBA code into  
> > Ruby. Here are the VBA codes:
> > 
> > Sub AddNew()  
> > Set NewBook = Workbooks.Add  
> > &nbsp;&nbsp;&nbsp;&nbsp;With NewBook  
> > &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Title = "All Sales"  
> > &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Subject = "Sales"  
> > &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.SaveAs Filename:="Allsales.xls"  
> > &nbsp;&nbsp;&nbsp;&nbsp;End With  
> > End Sub
> > 
> > And here are some Ruby codes:

---

<div class="post-metadata">

**Author:** ![Guest2](https://avatars.discourse-cdn.com/v4/letter/g/439d5e/32.png) [@Guest2](https://rubytalk.org/u/Guest2)\
**Post date:** [20 November 2006 14:22 UTC](https://rubytalk.org/t/how-to-translate-a-vba-code-into-ruby-script/33086/3 "2006-11-20T14:22:10Z")

</div>

Hi Jan,

Thank you very much. I think I am on the right track now.

Li

> **···**
>
> --  
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).

---

<div class="post-metadata">

**Author:** ![Benjohn\_Barnes](https://avatars.discourse-cdn.com/v4/letter/b/df705f/32.png) [@Benjohn\_Barnes](https://rubytalk.org/u/Benjohn_Barnes)\
**Post date:** [20 November 2006 16:11 UTC](https://rubytalk.org/t/how-to-translate-a-vba-code-into-ruby-script/33086/4 "2006-11-20T16:11:38Z")

</div>

I expect you can also do:  
newbook=excel.Workbooks.Add  
newbook.instance\_eval do  
&nbsp;&nbsp;Title = "All Sales"  
&nbsp;&nbsp;Subject = "Sales"  
&nbsp;&nbsp;SaveAs = Filename:="Allsales.xls"  
end

🙂 But I'm guessing. I'd be interested to know if it works, but it would  
be a closer mapping to VB's "with" expression.

> **···**
>
> > On 11/20/06, Li Chen \<chen\_li3@yahoo.com\> wrote:
> > 
> > > Hi all,
> > > 
> > > I need to use WIN32OLE to do some Excel automation. I read some  
> > > documents and search google.The examples of how to use WIN32OLE are  
> > > almost the same. But none of them show how to translate a VBA code  
> > > into  
> > > Ruby. Here are the VBA codes:
> > > 
> > > Sub AddNew()  
> > > Set NewBook = Workbooks.Add  
> > > &nbsp;&nbsp;&nbsp;&nbsp;With NewBook  
> > > &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Title = "All Sales"  
> > > &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Subject = "Sales"  
> > > &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.SaveAs Filename:="Allsales.xls"  
> > > &nbsp;&nbsp;&nbsp;&nbsp;End With  
> > > End Sub
> > > 
> > > And here are some Ruby codes:
> > 
> > require 'win32ole'
> > 
> > excel=WIN32OLE.new('Excel.Application')  
> > #Set NewBook = Workbooks.Add  
> > newbook=excel.Workbooks.Add  
> > #With NewBook  
> > # .Title = "All Sales"  
> > newbook.Title = "All Sales"  
> > # .Subject = "Sales"  
> > newbook.Subject = "Sales"  
> > # .SaveAs Filename:="Allsales.xls"  
> > newbook.SaveAs Filename =\> "Allsales.xls"
