Prev: Metaprograming
Next: right*, not write
From: zak on 10 Jun 2010 11:44
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
From: Lars Olsson on 11 Jun 2010 08:45
On 10 Juni, 17:44, zak <u.got...(a)googlemail.com> 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
From: zak on 11 Jun 2010 10:06
On 11 Jun., 14:45, Lars Olsson <la...(a)lassoweb.se> wrote:
> On 10 Juni, 17:44, zak <u.got...(a)googlemail.com> 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
From: Lars Olsson on 16 Jun 2010 02:49
On 11 Juni, 16:06, zak <u.got...(a)googlemail.com> wrote:
> On 11 Jun., 14:45, Lars Olsson <la...(a)lassoweb.se> wrote:
>
>
>
>
>
> > On 10 Juni, 17:44, zak <u.got...(a)googlemail.com> 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
 | 
Pages: 1
Prev: Metaprograming
Next: right*, not write