Excel on screen

[esr]

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.

[Rhose]

If you want to work with an Excel speadsheet using perl, I HIGHLY suggest reading cacharbe's wonderful node Using Win32::OLE and Excel - Tips and Tricks.

It has helped me many times. (Thank you again, Cacharbe)

[Anonymous Monk]

Maybe this could help you get started:

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"); 

[esr]

Thanks. It was the "Visible" line that I needed to know about.

[Anonymous Monk]

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)

[quinkan]

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.

[dragonchild]

What's wrong with system( "excel.exe $filename" );?