Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

OLE and Excel question

by gibsonca (Beadle)
on May 05, 2009 at 20:56 UTC ( #762070=perlquestion: print w/replies, xml ) Need Help??
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 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

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
    This (translated to perl) works for me
    my $freeze_panes = $gExcel->ActiveSheet->Range("B2")->Select; #$gSheet->Cells(2,2)->Select(); $gExcel->ActiveWindow->{FreezePanes} = 1;
    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.

      My general approach is (very) slightly different:

      # The number of rows above the split: $Excel->ActiveWindow->{SplitRow} = 1; $Excel->ActiveWindow->{FreezePanes} = 1;

      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.

      Thanks. Your suggestion worked and my problem solved.
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;

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://762070]
Approved by AnomalousMonk
and the monastery is silent...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (5)
As of 2017-11-19 06:04 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (278 votes). Check out past polls.