OLE and Excel question

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

Using Windows XP, Perl 5.10.0, Win32 OLE, Excel 2003

1) Following code runs fine, just doesn't freeze pane of newly 
   created excel doc. The 2nd row is highlighted..is
   there something else to do make this happen?

  my $freeze_panes = $gExcel->ActiveSheet->Range("2:2")->Select;
  #$gSheet->Cells(2,2)->Select();
  $gExcel->ActiveSheet->{FreezePanes} = $TRUE;

2) On occasions users hang up excel such that they have to kill the 
   excel process with windows Task Manager. I'd like 
  to detect that excel is running. I've tried various 
   combinations with this. Any other ideas?

  $Excel = Win32::OLE->GetActiveObject('Excel.Application')
       || Win32::OLE->new('Excel.Application', 'Quit')) {


Thanks
[download]

Comment on OLE and Excel question Download Code

Replies are listed 'Best First'.
Re: OLE and Excel question by Sandy (Curate) on May 05, 2009 at 21:28 UTC
for your first question Sometimes it really helps to run the Excel macros and see what that gives you `Range("B2").Select ActiveWindow.FreezePanes = True` [download] This (translated to perl) works for me `my $freeze_panes = $gExcel->ActiveSheet->Range("B2")->Select; #$gSheet->Cells(2,2)->Select(); $gExcel->ActiveWindow->{FreezePanes} = 1;` [download] I changed `ActiveSheet` to `ActiveWindow` and set the selection to a single cell (instead of a row). If set to a row (2:2) it still works by freezing only the top row.	[reply] [d/l] [select]
Re^2: OLE and Excel question by Nkuvu (Priest) on May 05, 2009 at 21:48 UTC
My general approach is (very) slightly different: `# The number of rows above the split: $Excel->ActiveWindow->{SplitRow} = 1; $Excel->ActiveWindow->{FreezePanes} = 1;` [download] No selection or intermediate variable necessary (`$freeze_panes` -- used somewhere else in the code?). In regards to the second question, are you (by "you" I mean gibsonca) calling `$Excel->Quit()` when you're done with Excel? Also note that I've found that if my script terminates early, the application isn't always killed properly.	[reply] [d/l] [select]
Re^2: OLE and Excel question by gibsonca (Beadle) on May 06, 2009 at 21:55 UTC
Thanks. Your suggestion worked and my problem solved.	[reply]
Re: OLE and Excel question by ww (Archbishop) on May 06, 2009 at 02:10 UTC
re question 2, read: `perldoc Win32::OLE` and, deep into this dense document, find: `# save and exit $book->SaveAs( 'test.xls' ); undef $book; undef $ex;` [download]	[reply] [d/l] [select]