Noble monks-

I have a script that runs about 10 times per day. It uses Win32::OLE to open excel spreadsheets and refresh them (via MS Query and DB2) and then do stuff with the refreshed data. Every now and then, the script will hang. I think the hang is in either MS Query or Excel, but I thought I'd put the code up and see if anyone can point out an obvious problem I've missed.

I've tried to reduce the script down to just the relevant parts without cutting too much...I can expand a bit if needed. 

sub REFRESH_EXCEL{
#removed code to get the book name, etc..

  $book = $excel->Workbooks->Open("$programDir\\$filename");

#removed some error checking

  eval{
    &LOG("Refreshing all queries in this report.\n");
    $book->RefreshAll unless $debug > 2;  

    sleep 30;
    until(&REFRESH_COMPLETE($book)){
      sleep 30;
    }
  };
#removed code to do stuff with the refreshed workbook
}


sub REFRESH_COMPLETE{
  my $book = pop(@_);

  #for each worksheet in the workbook
  foreach my $sheet (in $book->{Sheets}){
    foreach my $qryTable (in $sheet->{QueryTables}){
      return undef if $qryTable->{Refreshing} > 0;
    }
  }

  return 1;
}

[download]
When the hang occurs, the last thing I see in the logs is the line "refreshing all..." from just after the eval. Any suggestions are welcome...

In reply to Hang during RefreshAll using Win32::OLE and Excel 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.