EXCEL Ruby not working

Discussion in 'Ruby' started by anon1m0us, Dec 15, 2006.

  1. anon1m0us

    anon1m0us Guest

    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()
    anon1m0us, Dec 15, 2006
    #1
    1. Advertising

  2. anon1m0us

    ChrisH Guest

    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
    ChrisH, Dec 15, 2006
    #2
    1. Advertising

  3. anon1m0us

    Tim Pease Guest

    On 12/15/06, ChrisH <> 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
    Tim Pease, Dec 15, 2006
    #3
  4. 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
    Stephan Kämper, Dec 16, 2006
    #4
  5. anon1m0us

    Li Chen Guest

    Li Chen, Dec 16, 2006
    #5
  6. 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
    Stephan Kämper, Dec 16, 2006
    #6
  7. anon1m0us

    anon1m0us Guest

    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
    anon1m0us, Dec 19, 2006
    #7
  8. anon1m0us

    anon1m0us Guest

    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
    anon1m0us, Dec 19, 2006
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,494
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  2. viza
    Replies:
    0
    Views:
    660
  3. Replies:
    5
    Views:
    235
  4. Ruhul Amin
    Replies:
    7
    Views:
    114
    Wang Dong
    Mar 29, 2007
  5. sed_y
    Replies:
    0
    Views:
    1,011
    sed_y
    Feb 15, 2012
Loading...

Share This Page