Member Avatar for 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 line 5.
Execution of 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;
  • 2 Contributors
  • 4 Replies
  • 967 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mitchems

Dani AI

Generated

A few concise notes that address the exact issues in the code you posted.

The immediate bug in your snippet is that you reuse the same lexical ($Book) for two different Workbooks; the second Open overwrites the first, so Personal.xls never gets saved/closed and Excel prompts on exit. The "Global symbol '$Excel' requires explicit package name" happens when you reference that variable before its my declaration — move any Application-property assignments to after you create the OLE object and keep distinct variables for each workbook. See the Win32::OLE reference for the GetActiveObject/new pattern. (mkssoftware.com)

Recommended workflow (keeps workbooks distinct, suppresses prompts, and closes cleanly):

  • Create/Get the Excel Application object.

  • Open Personal.xls and the target file into separate variables.

  • Run the macro from Personal (Application-level Run).

  • To avoid save prompts: either call Close with SaveChanges=False, or set the workbook's Saved property to True before closing so Excel treats it as already-saved. Use Application.DisplayAlerts = False while automation is running to suppress many alerts (but test — it does not replace explicitly choosing a SaveAs FileFormat). Example of the close/mark trick:

    # mark no-save and close without prompt
$target_wb->{Saved} = 1;
$target_wb->Close(0);
$personal_wb->Close(0);

    Documentation: Application.DisplayAlerts, Workbook.Saved and Workbook.Close. (learn.microsoft.com)

If your macro writes a delimited/text file, specify the FileFormat when using SaveAs so Excel does not prompt for formatting choices. Also be careful: when DisplayAlerts is False Excel can pick the default response for overwrite prompts — test to avoid accidental data loss. See the SaveAs documentation for FileFormat details. (learn.microsoft.com)

(As pointed out, disabling alerts helps; combine that with separate workbook variables and explicit Close/Saved handling to eliminate the popup.)

Recommended Answers

All 4 Replies

Member Avatar for 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.

Member Avatar for 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..

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.