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

If you can describe the problem you're having in a little more detail, I may be able to help.
  • Comment on Re^2: RFC: Win32::OLE and Excel's RefreshAll

Replies are listed 'Best First'.
Re^3: RFC: Win32::OLE and Excel's RefreshAll
by thoglette (Scribe) on Dec 13, 2007 at 12:55 UTC
    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!
      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.
      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; }