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