EXCEL Ruby not working

A

anon1m0us

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").NumberFormat = ("yyyy-mm-dd
hh:mm:ss")
excel.Worksheets("Sheet1").Range("a1:g1").Font.Bold = 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()
 
C

ChrisH

anon1m0us said:
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
 
T

Tim Pease

...

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
 
S

Stephan Kämper

Hi,
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
 
A

anon1m0us

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?
 
A

anon1m0us

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,717
Messages
2,569,381
Members
44,699
Latest member
ChristaBya

Latest Threads

Top