Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > EXCEL Ruby not working

Reply
Thread Tools

EXCEL Ruby not working

 
 
anon1m0us
Guest
Posts: n/a
 
      12-15-2006
I get a file name in the directory and modify the cells with making
bold etc.
when excel.visible=TRUE it works. However, when excel.visible=FALSE
nothing happens.
Why?



newdir=Dir.getwd
x=1
excel = WIN32OLE::new("excel.application")
excel.visible=FALSE
Dir["*.xls"].each { |file|


book = excel.Workbooks.Open("#{newdir}\\#{file}")
sheetname=File.basename("#{file.upcase}",".XLS")
excel.Worksheets("#{sheetname}").Name="Sheet1"

excel.Worksheets("Sheet1").Range("A:A").NumberForm at = ("yyyy-mm-dd
hh:mm:ss")
excel.Worksheets("Sheet1").Range("a1:g1").Font.Bol d = 1
excel.Worksheets("Sheet1").Range("a1:g1").Interior .ColorIndex = 6
excel.Worksheets("Sheet1").Columns.AutoFit
excel.Worksheets("Sheet1").Select
excel.Worksheets("Sheet1").Rows("2:2").Select
excel.ActiveWindow.FreezePanes =1

excel.DisplayAlerts = 0
book.SaveAs("#{file}")
excel.DisplayAlerts = 1
book.close true

}
excel.quit()

 
Reply With Quote
 
 
 
 
ChrisH
Guest
Posts: n/a
 
      12-15-2006

anon1m0us wrote:
> I get a file name in the directory and modify the cells with making
> bold etc.
> when excel.visible=TRUE it works. However, when excel.visible=FALSE
> nothing happens.
> Why?

....

Tried your code and it seems that 'Save as...' is putting the saved
file into 'My Documents' rather than overwriting the original (which is
in the same dir as where I saved your code into a file).

Cheers
Chris

 
Reply With Quote
 
 
 
 
Tim Pease
Guest
Posts: n/a
 
      12-15-2006
On 12/15/06, ChrisH <(E-Mail Removed)> wrote:
>
> anon1m0us wrote:
> > I get a file name in the directory and modify the cells with making
> > bold etc.
> > when excel.visible=TRUE it works. However, when excel.visible=FALSE
> > nothing happens.
> > Why?

> ...
>
> Tried your code and it seems that 'Save as...' is putting the saved
> file into 'My Documents' rather than overwriting the original (which is
> in the same dir as where I saved your code into a file).
>


I have run into trouble in the past using relative pathnames for MS
Office files (open, saving). Try using an absolute pathname when you
(a) open the file and (b) save the file. That should put it in the
correct location.

Blessings,
TwP

 
Reply With Quote
 
Stephan Kämper
Guest
Posts: n/a
 
      12-16-2006
Hi,

anon1m0us wrote:
> I get a file name in the directory and modify the cells with making
> bold etc.
> when excel.visible=TRUE it works. However, when excel.visible=FALSE
> nothing happens.
> Why?
>
>
>
> newdir=Dir.getwd
>
> }
> excel.quit()
>


This will look more like Ruby is you don't type the parentheses, and
it's less typing.

Speaking of Excel, I mostly use spreadsheet and parseexcel, which are
both available here:

http://rubyforge.org/frs/?group_id=678&release_id=6674

And BTW, you might consider putting your code in a begin ... rescue ...
ensure ... end block and, well, ensure that excel it actually quit.
Otherwise you might (will) end up with 'dangling' excel instances if (or
when) the code raises an exception.
If/when that happens, you have to quit the dangling Excel app. from the
task manager before you can run your program again (well without
problems, that is).
This happened every now and then on a Windows (2K) machine until I made
sure that excel.quit is actually executed in case of an error.

Anyway, I think the above mentioned modules are easier to use and
understand than using OLE automation. And the code also look a lot more
like Ruby.

Happy rubying

Stephan



 
Reply With Quote
 
Li Chen
Guest
Posts: n/a
 
      12-16-2006
Stephan Kämper wrote:

> Speaking of Excel, I mostly use spreadsheet and parseexcel, which are
> both available here:
>
> http://rubyforge.org/frs/?group_id=678&release_id=6674


Is it possible to draw graphics with it in Excel?

Li

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Stephan Kämper
Guest
Posts: n/a
 
      12-16-2006
Li Chen wrote:
> Stephan Kämper wrote:
>
>> Speaking of Excel, I mostly use spreadsheet and parseexcel, which are
>> both available here:
>>
>> http://rubyforge.org/frs/?group_id=678&release_id=6674

>
> Is it possible to draw graphics with it in Excel?
>


I never tried to, so I'm not sure.

Stephan

 
Reply With Quote
 
anon1m0us
Guest
Posts: n/a
 
      12-19-2006
The main issue is still unresolved....why when I have the excel not
visible NOTHING happens. Only when i make excel =true(visable) then
everyting occurs?



Stephan Kämper wrote:
> Li Chen wrote:
> > Stephan Kämper wrote:
> >
> >> Speaking of Excel, I mostly use spreadsheet and parseexcel, which are
> >> both available here:
> >>
> >> http://rubyforge.org/frs/?group_id=678&release_id=6674

> >
> > Is it possible to draw graphics with it in Excel?
> >

>
> I never tried to, so I'm not sure.
>
> Stephan


 
Reply With Quote
 
anon1m0us
Guest
Posts: n/a
 
      12-19-2006
The main issue is still unresolved....why when I have the excel not
visible NOTHING happens. Only when i make excel =true(visable) then
everyting occurs?



Stephan Kämper wrote:
> Li Chen wrote:
> > Stephan Kämper wrote:
> >
> >> Speaking of Excel, I mostly use spreadsheet and parseexcel, which are
> >> both available here:
> >>
> >> http://rubyforge.org/frs/?group_id=678&release_id=6674

> >
> > Is it possible to draw graphics with it in Excel?
> >

>
> I never tried to, so I'm not sure.
>
> Stephan


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Not able to display color in excel not able to display color in excel using xml sed_y XML 0 02-15-2012 09:46 PM
Re: Open an Excel File in Excel, not HTML viza HTML 0 07-07-2008 11:04 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
excel is not closed in runtime using excel.Quit() Ruhul Amin Ruby 7 03-29-2007 03:56 PM
Using the SaveAs in Excel not working with Ruby anon1m0us@yahoo.com Ruby 5 12-12-2006 08:57 PM



Advertisments