Re: RFC: Win32::OLE and Excel's RefreshAll

[thoglette]

I'm sitting here with exactly the same problem (in this case complicated by the Excel spreadsheet modifying itself and/or being openned by other processes).

So I'm also struggling with trying to translate Open(ReadOnly:=True) and Close(SaveChanges:=False).

And deal with the file-already-open problems

[jrsimmon]

If you can describe the problem you're having in a little more detail, I may be able to help.

[thoglette]

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.

[jrsimmon]

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.

[jrsimmon]

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;
}