win32ole excel excelconst disable macros

Discussion in 'Ruby' started by zak, Jun 10, 2010.

  1. zak

    zak Guest

    Hi!

    I use

    book = self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)

    to never update links in excel files when opend with ruby.

    is there an excelconst to disable macros?
    i can't find anything.

    Thanks,
    uwe
    zak, Jun 10, 2010
    #1
    1. Advertising

  2. zak

    Lars Olsson Guest

    On 10 Juni, 17:44, zak <> wrote:
    > Hi!
    >
    > I use
    >
    > book = self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)
    >
    > to never update links in excel files when opend with ruby.
    >
    > is there an excelconst to disable macros?
    > i can't find anything.
    >
    > Thanks,
    > uwe


    Provided that self.excel represents the Excel.Application object, you
    can use the following code to prevent macros from running
    automatically:

    self.excel.EnableEvents = false
    book = self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)
    self.excel.EnableEvents = true

    /lasso
    Lars Olsson, Jun 11, 2010
    #2
    1. Advertising

  3. zak

    zak Guest

    On 11 Jun., 14:45, Lars Olsson <> wrote:
    > On 10 Juni, 17:44, zak <> wrote:
    >
    > > Hi!

    >
    > > I use

    >
    > > book = self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)

    >
    > > to never update links in excel files when opend with ruby.

    >
    > > is there an excelconst to disable macros?
    > > i can't find anything.

    >
    > > Thanks,
    > > uwe

    >
    > Provided that self.excel represents the Excel.Application object, you
    > can use the following code to prevent macros from running
    > automatically:
    >
    > self.excel.EnableEvents = false
    > book = self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)
    > self.excel.EnableEvents = true
    >
    > /lasso


    Thanks Lasso,

    yes, self.excel represents the Excel.Application object:

    class ExcelConst
    # to be filled with all Excelconstants
    # once excel is running
    end

    #---------------------------------------------------------------
    # make an Excel class to simplify opening & closing of classes
    # place this in a separate file, and just require it
    #---------------------------------------------------------------
    class Excel
    attr_accessor :excel
    def initialize
    require 'win32ole'
    @excel = WIN32OLE::new('Excel.Application')
    if !(defined? ExcelConst::XlYes)
    WIN32OLE.const_load(@excel, ExcelConst)
    end
    @excel.DisplayAlerts = false
    @excel.Visible = false
    yield self
    @excel.Quit
    end

    def open_book file
    self.excel.EnableEvents = false
    book =
    self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)
    #self.excel.EnableEvents = true
    yield book
    self.excel.ActiveWorkbook.Close(0)
    end
    end

    The reason why I posted the original message was, that excel opend
    visible during runtime although I set
    @excel.Visible = false
    I thought the reason for that behavior could be conncted to macros.
    But it wasn't. I inserted your code and still excel opens the workbook
    during:

    Excel.new do |xl| # instanciate excel

    filesL.each_with_index do |file,i| # let's loop on each file
    i=3-i # array index starts at 0

    xl.open_book(file) do |workbook|
    sheets = workbook.Worksheets("Statistik")
    rows = sheets.UsedRange.Rows.Count
    cells = sheets.Range("C1:E#{rows}")
    cells.each do |cell| ...........

    Any ideas?
    Have a nice weekend,

    uwe
    zak, Jun 11, 2010
    #3
  4. zak

    Lars Olsson Guest

    On 11 Juni, 16:06, zak <> wrote:
    > On 11 Jun., 14:45, Lars Olsson <> wrote:
    >
    >
    >
    >
    >
    > > On 10 Juni, 17:44, zak <> wrote:

    >
    > > > Hi!

    >
    > > > I use

    >
    > > > book = self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)

    >
    > > > to never update links inexcelfiles when opend with ruby.

    >
    > > > is there an excelconst to disable macros?
    > > > i can't find anything.

    >
    > > > Thanks,
    > > > uwe

    >
    > > Provided that self.excelrepresents theExcel.Application object, you
    > > can use the following code to prevent macros from running
    > > automatically:

    >
    > > self.excel.EnableEvents = false
    > > book = self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)
    > > self.excel.EnableEvents = true

    >
    > > /lasso

    >
    > Thanks Lasso,
    >
    > yes, self.excelrepresents theExcel.Application object:
    >
    > class ExcelConst
    >     # to be filled with all Excelconstants
    >     # onceexcelis running
    > end
    >
    > #---------------------------------------------------------------
    > # make anExcelclass to simplify opening & closing of classes
    > # place this in a separate file, and just require it
    > #---------------------------------------------------------------
    > classExcel
    >     attr_accessor :excel
    >     def initialize
    >         require 'win32ole'
    >         @excel= WIN32OLE::new('Excel.Application')
    >         if !(defined? ExcelConst::XlYes)
    >             WIN32OLE.const_load(@excel, ExcelConst)
    >         end
    >         @excel.DisplayAlerts = false
    >         @excel.Visible = false
    >         yield self
    >         @excel.Quit
    >     end
    >
    >     def open_book file
    >         self.excel.EnableEvents = false
    >         book =
    > self.excel.Workbooks.Open(file,ExcelConst::XlUpdateLinksNever)
    >         #self.excel.EnableEvents = true
    >         yield book
    >         self.excel.ActiveWorkbook.Close(0)
    >     end
    > end
    >
    > The reason why I posted the original message was, thatexcelopend
    > visible during runtime although I set
    > @excel.Visible = false
    > I thought the reason for that behavior could be conncted to macros.
    > But it wasn't. I inserted your code and stillexcelopens the workbook
    > during:
    >
    > Excel.new do |xl|    # instanciateexcel
    >
    >     filesL.each_with_index do |file,i|  # let's loop on each file
    >         i=3-i # array index starts at 0
    >
    >         xl.open_book(file) do |workbook|
    >             sheets = workbook.Worksheets("Statistik")
    >             rows = sheets.UsedRange.Rows.Count
    >             cells = sheets.Range("C1:E#{rows}")
    >             cells.each do |cell| ...........
    >
    > Any ideas?
    > Have a nice weekend,
    >
    > uwe- Dölj citerad text -
    >
    > - Visa citerad text -


    I'm afraid I cannot reproduce the problem (using ruby 1.9.1p378
    (2010-01-10 revision 26273) [i386-mswin32] and Excel XP SP3). Using
    your code, I can read and write to workbooks without ever seeing the
    Excel window. It does not matter if the workbooks contains macros or
    not or if Excel is already opened. Your code works perfectly here...

    /lasso
    Lars Olsson, Jun 16, 2010
    #4
    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. Replies:
    80
    Views:
    2,416
    Stephen J. Bevan
    Nov 7, 2003
  2. Replies:
    1
    Views:
    438
    Marco Antoniotti
    Oct 7, 2003
  3. Replies:
    5
    Views:
    492
  4. Masaki Suketa
    Replies:
    4
    Views:
    375
    Dave Burt
    Mar 27, 2006
  5. zxem
    Replies:
    1
    Views:
    228
Loading...

Share This Page