Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Hang during RefreshAll using Win32::OLE and Excel

by jrsimmon (Hermit)
on Mar 05, 2010 at 20:52 UTC ( [id://827064]=perlquestion: print w/replies, xml ) Need Help??

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

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://827064]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2025-02-07 14:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Which URL do you most often use to access this site?












    Results (94 votes). Check out past polls.