tag:blogger.com,1999:blog-284600789737488130.post9197512406597326375..comments2024-02-07T02:22:20.642-05:00Comments on Ruby on Windows: Automating Excel with RubyDavid Mullethttp://www.blogger.com/profile/05597492688893461137noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-284600789737488130.post-67453456551929808512016-08-10T21:06:28.314-05:002016-08-10T21:06:28.314-05:00@Anonymous:
Call the Worksheets.Add() method and ...@Anonymous:<br /><br />Call the Worksheets.Add() method and pass it two arguments:<br /><br />- nil<br />- an object that represents the last item in the Worksheets collection<br /><br />Example, where wb is your open workbook:<br /><br />last_worksheet = wb.Worksheets(wb.Worksheets.Count)<br />ws = wb.Worksheets.Add(nil, last_worksheet)<br />David Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-11530546757930506872016-08-07T02:21:54.967-05:002016-08-07T02:21:54.967-05:00Can you please tell me how i can open the existing...Can you please tell me how i can open the existing excel file and dd worksheet at the end with out over righting the already existing worksheets.......<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-41854285334551675342014-08-05T05:02:45.170-05:002014-08-05T05:02:45.170-05:00Excellent Post...Keep up the good work.Excellent Post...Keep up the good work.Sagarhttps://www.blogger.com/profile/06686177535004668246noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-55218825954096283772012-03-20T20:08:13.065-05:002012-03-20T20:08:13.065-05:00@K.Funky:
You can call the Ruby interpreter and r...@K.Funky:<br /><br />You can call the Ruby interpreter and run a Ruby script from within a VBA macro, via the Shell command:<br /><br />Sub Macro1()<br /> Shell "ruby.exe C:\my_script.rb", vbNormalFocus<br />End Sub<br /><br />The first parameter is the shell command to execute. The second parameter is the WindowState, which can be one of the following Excel constants:<br /><br />vbHide<br />vbMaximizedFocus<br />vbMinimizedFocus<br />vbMinimizedNoFocus<br />vbNormalFocus<br />vbNormalNoFocus<br /><br />-DavidDavid Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-46557002849523998292012-03-20T15:13:09.499-05:002012-03-20T15:13:09.499-05:00Hi,
this looks very interesting. I am wondering ho...Hi,<br />this looks very interesting. I am wondering how the other direction would work. Is there a way to call Ruby code from within Excel, similar as you would call a VBA macro?<br />Thanks for any hints.K.Funkyhttps://www.blogger.com/profile/09756591051268088076noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-63255975338971425962012-03-20T13:48:04.125-05:002012-03-20T13:48:04.125-05:00Hi,
this really is very interesting. The question ...Hi,<br />this really is very interesting. The question I have concerning the Ruby Office interaction:<br />Is there a way to call ruby code from within Excel, similar as I would call a VBA macro?<br />Thanks for any hint on this issueK.Funkyhttps://www.blogger.com/profile/09756591051268088076noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-59622326044357889472011-08-17T17:54:22.574-05:002011-08-17T17:54:22.574-05:00Hi...sorry but I've a bit problem..when I save...Hi...sorry but I've a bit problem..when I save the file..It's only for read and I can't write (I've change the permission manually)...can I do the file be save for read-write?..thanks.....Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-67709949872178978142011-05-30T06:51:28.014-05:002011-05-30T06:51:28.014-05:00@Jim: Assuming that you have no typos in your code...@Jim: Assuming that you have no typos in your code, it appears there may be an issue with your installation of Excel. Note the line in the error "The COM+ registry database detected a system error". Reinstall Excel or repair your installation and send me an email if you are still having problems.David Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-26931380009886723272011-05-27T03:55:06.100-05:002011-05-27T03:55:06.100-05:00Hi,
Great tutorial! However, when I try to run t...Hi, <br /><br />Great tutorial! However, when I try to run the example, I get the following error:<br /><br />C:/Users/Jim/My Documents/Aptana Studio 3 Workspace/ExcelTest/ExcelTest.rb:3:in `initialize': failed to create WIN32OLE object from `Excel.Application' (WIN32OLERuntimeError)<br /> HRESULT error code:0x80110474<br /> The COM+ registry database detected a system error<br /> from C:/Users/Jim/My Documents/Aptana Studio 3 Workspace/ExcelTest/ExcelTest.rb:3:in `new'<br /> from C:/Users/Jim/My Documents/Aptana Studio 3 Workspace/ExcelTest/ExcelTest.rb:3:in `'<br /><br />Any ideas what might be wrong?<br /><br />Thanks!<br /><br />JimJimhttps://www.blogger.com/profile/16451364608749003784noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-52764555308099150332007-04-11T08:42:00.000-05:002007-04-11T08:42:00.000-05:00Hey, thanks for these posts !Looks like quite usef...Hey, thanks for these posts !<BR/><BR/>Looks like quite useful info.<BR/>I plan to use some of this info in some upcoming open source projects and technical articles. Will mention that I got good info from your blog.<BR/><BR/>Vasudev Ram<BR/>Site: http://www.dancingbison.com<BR/>Blog: http://jugad.livejournal.comVasudev Ramhttps://www.blogger.com/profile/13568740634188042591noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-20322596173002112402007-04-09T12:05:00.000-05:002007-04-09T12:05:00.000-05:00Thanks for the hint!(Stefan E)Thanks for the hint!<BR/>(Stefan E)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-41942704095588069892007-04-08T17:21:00.000-05:002007-04-08T17:21:00.000-05:00Stefan-The example is somewhat incomplete, in that...Stefan-<BR/><BR/>The example is somewhat incomplete, in that it assumes that <B>data</B> represents a 2-dimensional array that you have already created, perhaps from a database query, reading data from a file, etc..David Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-63690893592002089412007-04-08T14:46:00.000-05:002007-04-08T14:46:00.000-05:00Hmm, am I missing something?Where does data come f...Hmm, am I missing something?<BR/>Where does data come from?<BR/><BR/>C:/createExcel.rb:14: undefined local variable or method `data' for main:Object (NameError)<BR/><BR/>But anyway. A cool example!<BR/>Stefan E.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-78246875477409801942007-03-18T08:37:00.000-05:002007-03-18T08:37:00.000-05:00Good points, pitsula!I'm very comfortable with COM...Good points, pitsula!<BR/><BR/>I'm very comfortable with COM automation, due to years of working with Microsoft VB and COM (we all have our dark sides), and the fact that Microsoft Office is always installed on all my users' PCs. As such, I haven't worked with the spreadsheet libraries you mentioned, but plan to in the near future.<BR/><BR/>For the sake of brevity, code snippets that I post here usually lack the error handling and such that should be included in a complete script/application. You provide a good example of using the 'ensure' statement to ensure that the Excel application exits, even in the event of an error.<BR/><BR/>Thanks!David Mullethttps://www.blogger.com/profile/05597492688893461137noreply@blogger.comtag:blogger.com,1999:blog-284600789737488130.post-38733135873964544292007-03-18T05:25:00.000-05:002007-03-18T05:25:00.000-05:00Hi,personally, I rather use spreadsheet-excel and ...Hi,<BR/><BR/>personally, I rather use spreadsheet-excel and parseexcel (<A HREF="http://rubyforge.org/projects/spreadsheet/" REL="nofollow">spreadsheet @ Rubyforge</A>).<BR/>The interface seems simpler and more rubyesuqe to me. And you don't need to have Excel installed on the machine.<BR/>Apart from that you might consider wrapping the code in a begin ... ensure: <BR/><BR/>require 'win32ole'<BR/><BR/>begin<BR/> excel = WIN32OLE.new( "excel.application" )<BR/> # Use Excel here...<BR/> excel.workbooks.close<BR/>rescue => e<BR/> # Handle exception...<BR/>ensure<BR/> excel.Quit<BR/>end<BR/><BR/>That way you're pretty sure that the Excel application will in fact be quit when the script is done. Otherwise you'll have a dangling Excel instance, which might prevent a rerun of the program. At least that's what I experienced.Stephanhttps://www.blogger.com/profile/11443614880191569272noreply@blogger.com