http://www.perlmonks.org?node_id=787586

fish has asked for the wisdom of the Perl Monks concerning the following question:

I have the following subroutine:
sub OpenAndSaveXLS{ (my $xlsfile)=@_; if((-e $xlsfile) and (!isFileInUse($xlsfile))){ $xlsfile =~ s/\//\\/g; print "xls: ".$xlsfile . "\n\n"; my $ex_ol = Win32::OLE::Const->Load("Microsoft Excel 12.0 Obje +ct Library"); my $Excel = Win32::OLE->GetActiveObject('Excel.Application')|| + Win32::OLE->new('Excel.Application', 'Quit'); $Excel->Application->{DisplayAlerts} = 0; my $book = $Excel->Workbooks->Open("$xlsfile"); $Excel->ActiveWorkbook->SaveAs({FileName=>"$xlsfile", FileForm +at=>$$ex_ol{'xlWorkbookNormal'}}); $book->Close(); $Excel->Quit(); return 0; } }
If I use this subroutine once everything is fine, but if I do it twice the following error occures: Win32::OLE(0.1709) error 0x80010108: "Das aufgerufene Objekt wurde von den Clients getrennt" in METHOD/PROPERTYGET "Application" at Helper.pm line 233 Can't use an undefined value as a HASH reference at Helper.pm line 233. I call it like this:
Helper::OpenAndSaveXLS("D:/foo.xls"); Helper::OpenAndSaveXLS("D:/foo2.xls");
If I do it in debugging-mode, everything works fine. Obviously the first OpenAndSaveXLS-call is not finished when the 2nd starts.

Does anybody has an idea why this happens?

Best regards, fish

Replies are listed 'Best First'.
Re: Win32::OLE called by subroutine only workes once
by Bloodnok (Vicar) on Aug 11, 2009 at 12:09 UTC
    I don't know, but would guess that M$ have implemented the close call to return immediately after the close has been issued downward i.e. not waiting for completion of the closure.

    Try putting a sleep between the calls ... a near equivalent of running the code in the debugger when the delay would be mandraulic ;-|

    Just a thought...

    A user level that continues to overstate my experience :-))
      Thanks for your ideas.
      $book->Close(1); instead of $book->Close(); seems to work - but please do not ask me why :D

      Regards, fish
        Ok, obviously it was just fortune that it worked with close(1).

        Today I want to use the script again and it fails saying: Win32::OLE(0.1709) error 0x80010108: "Das aufgerufene Objekt wurde von den Clients getrennt"

        Does anybody has an idea how I can solve this problem?
Re: Win32::OLE called by subroutine only workes once
by Anonymous Monk on Aug 11, 2009 at 11:57 UTC
    Which method call triggers that error?
    ole->foo... or die $^E;