For the past week, I have been porting to perl a VB application used to automate the data refresh of an excel workbook containing one or more spreadsheets, each of which may contain any number query tables. During this process, the following problem presented itself: How can I tell when excel has finished refreshing all of the queries contained within the workbook? Having failed to find any examples pertaining to this specific problem, either within PM or without, I humbly present this (simplified) solution for consideration. 
#!c:/perl/bin
use strict;
use warnings;
use Win32::OLE qw(in with);

#I strongly recommend a user defined error handler when 
#using Win32::OLE with excel and refreshAll.  You must 
#have a way to handle the Application Busy error that 
#occurs when Microsoft Query is holding excel hostage.
Win32::OLE->Option(Warn => sub {&CHECK_OLE_ERROR;});

my $filename = "my_book.xls";
my $sleep_time = 30;
#I always want a new instance of excel, so that I can turn off all use
+r interaction
my $excel = Win32::OLE->new('Excel.Application', 'Quit') or die "Unabl
+e to open Excel";

#For my purposes, this application runs behind the 
#scenes and should never be interacting with a user.  
#Hence I turn off the following properties.
$excel->{Application}->{DisplayAlerts} = 0;
$excel->{Visible} = 0;

my $book = $excel->Workbooks->Open("$filename")or die "Unable to open 
+$filename";
$book->RefreshAll; #refreshAll does not having a meaningful return val
+ue, to the best of my knowledge

until(&REFRESH_COMPLETE($book)){
  sleep $sleep_time;
}

$book->Save or warn "Unable to save changes to $filename!";
$book->Close or warn "Unable to gracefully close $filename!";
undef $book;
undef $excel;

sub REFRESH_COMPLETE{
  #this code has been moved into a subroutine because 
  #each loop would periodically return undef prior to     
  #completly traversing its hash.  I have not been able 
  #to fully determine the cause of this, short of believing
  #it occurs when OLE hits an Application Busy exception.
  #By moving the code to a subroutine, I am able to 
  #safely reset the loop whenever an undef is encountered.
  #I am very interested in any more elegant solutions 
  #to this problem.

  my $book = pop(@_);

  #for each worksheet in the workbook
  foreach my $sheet (in $book->{Sheets}){
    return undef unless defined $sheet;
    #look for any queries in this worksheet
    foreach my $qryTable (in $sheet->{QueryTables}){
      return undef unless defined $qryTable;
      return undef if $qryTable->{Refreshing} > 0;
    }
  }
  return 1;
}

sub CHECK_OLE_ERROR{
  my $status = Win32::OLE->LastError();
  #application busy--just give it some time 
  if($status =~ /0x8001010a/){
    sleep $sleep_time;
    return 1;
  }else{
      die Win32::OLE->LastError();
  }
}

[download]
Further details available
The full version of this application runs eternally on a win2003 server. It reads a list of reports to refresh and does so at the appropriate time (typically triggered by the presence of a "trigger" file). Most reports update charts in the first few pages of the workbook. As each workbook is refreshed it mails either the workbook or a pdf (thank you http://www.ghostscript.com/awki) to the appropriate persons as determined by the .ini file.

In reply to RFC: Win32::OLE and Excel's RefreshAll by jrsimmon

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.