Running Macro on Excel through Perl with Two workbooks Open

[Grep]

Hi all,

In order to run an excel macro, I need my personal.xls excel file open so I open it along with the file I'd like to run the macro in. Problem is when I close the program I use save/close. But for some reason it still pops up a warning box saying: Would you like to save file.xls? Maybe since I'm opening two files the Save option isn't working on both. I tried doing save/close twice. But that isn't working either.

If i try to use the following in line 5: $Excel->{DisplayAlerts}=0;
I get this error:
Global symbol "$Excel" requires explicit package name at m2.pl line 5.
Execution of m2.pl aborted due to compilation errors.

Any Ideas? I'm using ActivePerl...

Here's what the code looks like:

use Win32::OLE::Variant;
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')||Win32::OLE->new('Excel.Application', 'Quit'); # use the Excel application


my $Book = $Excel->Workbooks->Open( "C:\\Documents and Settings\\durrani_K\\Application Data\\Microsoft\\Excel\\XLSTART\\Personal.xls" ); # Open Macro Workbook. 
my $Book = $Excel->Workbooks->Open( "c:\\test\\data\\file.xls" ); # Open Workbook needing the macro: Macro1

my $Sheet = $Book->Worksheets(1); #work on sheet1


$Excel->Run("Personal.xls!Macro1"); #Calling on the Macro from macro workbook.

printf ("the value is %s \n",$Sheet->Cells(1,1)->{'Value'});

$Book->Save; 
$Book->Close;

[mitchems]

Here is some code from a program that I wrote that saves a file in OLE/Excel:

my($xapp) = CreateObject OLE 'Excel.Application' || die $!;
$xapp->{'Visible'} = 1;
# Open the excel file
$wkb = $xapp->Workbooks->Open($template);
if ( ! $wkb )
{ print "Can't open $template\n"; exit; }	
 $wks = $wkb->Worksheets("Sheet_Name");

#more code removed


$file="\\report\.xls";
if ( -f $file )
{ unlink $file; }
$wkb->SaveAs({Filename => $file});
$wkb->Close();
$xapp->Quit();

I use a template that I open at the top of the file and then save the file under another name. I remove the existing file (with unlink) first.

[Grep]

Thanks Mitchems but that's still not working. I think my problem is that Excel wants to find out whether how to format the delimited file. I set up the macro to auto-save also and that doesn't get me past that screen either.

Maybe if we can get the code:
$Excel->{DisplayAlerts}=0;
working on my script, that'll pass the part where it wants to know what format I wnat to save my file in..

[Grep]

I went ahead and just ended my Macro excel with the autosave format=xlnormal or something along those lines, so it specifies the format, thus no more popup on it trying to figure out what format i want it in. Thanks again for the speedy reply mitchems.

[mitchems]

No problem. BTW, try:

$Excel->{'DisplayAlerts'} = 0;

with the single quotes. I have found that to work on occasion, but some alerts are not suppressed.