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;
}
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...