in reply to Re^2: RFC: Win32::OLE and Excel's RefreshAll
in thread RFC: Win32::OLE and Excel's RefreshAll

Sorry for the delay - had some more visible fires to extinguish.

I have an XLS from which I'm generating XML (and then HTML etc etc) The catches are two fold

  1. the XLS has some convoluted functions which mean that openning it changes it.
  2. the XLS may be currently open by a human user (adding or correcting data)
So I need to either open-it read-only and/or close it with don't save. Now I believe that the correct VB is Open(ReadOnly:=True) and/or Close(SaveChanges:=False). But so far(*) I've been unable to convert this into an effective representation in Perl

* I've only had time to put about an hour into this problem - as it works OK except in the cases listed above.


Butlerian Jihad now!

Replies are listed 'Best First'.
Re^4: RFC: Win32::OLE and Excel's RefreshAll
by jrsimmon (Hermit) on Dec 13, 2007 at 16:24 UTC
    If all you're doing is reading data from the workbook, Win32::OLE may be a bit of overkill. You don't need all of the application control that it provides. Have you looked at the plethora of other excel modules available on cpan? Spreadsheet::ParseExcel would seem to be a candidate, if you can live with the limitations (no formula support).

    Now, if you do in fact need the application control provided by Win32::OLE, then it sounds like you would want the read-only flag. Just closing without saving changes could leave you with problems if another user has the spreadsheet open. I don't know that off the top of my head, but I'll see if I can track it down.

    Update: Spreadsheet::Read looks like an even better match.
[reply]
Re^4: RFC: Win32::OLE and Excel's RefreshAll
by jrsimmon (Hermit) on Dec 19, 2007 at 16:17 UTC
    Sorry this is a little late in coming, but here's how to check the read only flag.
    use strict;
use warnings;
use Win32::OLE;

my $xlsFile = "c:\\temp\\myexcelfile.xls";
my $excel = Win32::OLE->new('Excel.Application', 'Quit');
my $book = $excel->Workbooks->Open("$xlsFile") or die;
if($book->{ReadOnly}+0 > 0){#the funny +0 is an easy way to force the 
+scalar to be interpreted as a number
  warn "$xlsFile is open Read Only!";
  $book->Close;
  $book = undef;
  $excel = undef;
}

    [download]
[reply]
[d/l]

In Section Meditations