Just another Perl shrine PerlMonks

### Excel on screen

by esr (Scribe)
 on Nov 22, 2004 at 20:31 UTC Need Help??
esr has asked for the wisdom of the Perl Monks concerning the following question:

I am not new to Perl but I am new to the use of the Win32::OLE morass. I have managed to get some basic things to work in opening an Excel spreadsheet etc. but have not yet found how to get the Excel spreadsheet to open and display on the screen. What I am trying to accomplish is to do some preliminary things from the Perl script, display the spreadsheet for manual editing, then return to the Perl script to read and process the spreadsheet data. I seem to be able to display the spreadsheet on the screen by going to the Windows Explorer and clicking on the file but I would like to do it from the Perl script.

Replies are listed 'Best First'.
Re: Excel on screen
by Rhose (Priest) on Nov 22, 2004 at 20:55 UTC
Re: Excel on screen
by Anonymous Monk on Nov 22, 2004 at 21:58 UTC
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

$Excel = Win32::OLE->new("Excel.Application");$Excel->{Visible} = 1;
$Book =$Excel->Workbooks->Open("C:/spreadsheet.xls");

Thanks. It was the "Visible" line that I needed to know about.
Re: Excel on screen
by Anonymous Monk on Nov 22, 2004 at 22:11 UTC
use OLE;

$file = 'c:\path\to\file.xls'; if(!-e$file)
{
print "File does not exist";
exit 0;
}

$app = CreateObject OLE 'Excel.Application' || die "Can't open EXCEL$
+!\n";

$book =$app->Workbooks->Open($file);$sheet = $book->Worksheets(1); # Retrieve$cellA1 = $sheet->Range("A1")->{'Value'};$cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n"; # Do pre-processing$sheet->Range("A1")->{'Value'} = 'pre';
$sheet->Range("B1")->{'Value'} = 'process';$cellA1 = $sheet->Range("A1")->{'Value'};$cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n";$app->{'Visible'} = 1;

# USER manual input happens here

# You can let them close when done (ctrl-f4 is quick)
while($app->ActiveWorkbook) { sleep(1); } # Or you can give them time on time sleep(10); # Close it up if you do time if($app->ActiveWorkbook)
{
$app->ActiveWorkbook->Close(0);$app->Quit();
}

# Do post-processing
$app = CreateObject OLE 'Excel.Application' || die "Can't open EXCEL$
+!\n";

$book =$app->Workbooks->Open($file);$sheet = $book->Worksheets(1); # Retrieve$cellA1 = $sheet->Range("A1")->{'Value'};$cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n"; # Do post-processing work$sheet->Range("A1")->{'Value'} = 'post';
$sheet->Range("B1")->{'Value'} = 'process';$cellA1 = $sheet->Range("A1")->{'Value'};$cellB1 = $sheet->Range("B1")->{'Value'}; print "|$cellA1|$cellB1|\n";$app->save();
$app->ActiveWorkbook->Close(0);$app->Quit();

# ThinMonk (TM)
[download]
Re: Excel on screen
by quinkan (Monk) on Nov 23, 2004 at 05:27 UTC
For all this and even more still, check out Win32::GuiTest in its latest version, found in Files at http://groups.yahoo.com/group/perlguitest/ And the recorder that comes with it should be able to generate your opening code for you.
Re: Excel on screen
by dragonchild (Archbishop) on Nov 22, 2004 at 20:35 UTC
What's wrong with system( "excel.exe \$filename" );?

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Create A New User
Node Status?
node history
Node Type: perlquestion [id://409724]
Approved by kvale
Front-paged by mattriff
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (4)
As of 2017-02-19 18:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
Before electricity was invented, what was the Electric Eel called?

Results (293 votes). Check out past polls.