Win32::OLE SaveAs Need to force Yes

P

Pat

I am having a problem. I want to open 600 excel files and add the
work REV A to the first cell. I am using Win32::OLE. Here is the
code. Very simple.
For every file that opens, I get a box that asks if I want to
overwrite the file. How do I force the answer to the popup to be yes?

#!c:/perl/bin/perl -w

use Win32::OLE;
$workdir = "c:\\temp\\GLNWI";

open (GLN_WI, "$workdir\\gln_file.txt") || die "Can't open gln_file";
@gln_wi = <GLN_WI>;
close GLN_WI;

foreach $file_name (@gln_wi) {
chomp $file_name;
$file_name = $workdir . "\\" . $file_name;
print "file_name = $file_name\n";
$excel = new Win32::OLE ('Excel.Application', 'Quit');
$excel->{Visible} = 0;
$excel->Workbooks->Open("$file_name") or die ("Error: unable to
open document ", Win32::OLE->LastError());
$excel->Range("A1")->{Value} = "Rev A";
$excel->SaveAs('$file_name');
undef $excel;

}
 
A

A. Sinan Unur

(e-mail address removed) (Pat) wrote in @posting.google.com:
I am having a problem. I want to open 600 excel files and add the
work REV A to the first cell. I am using Win32::OLE. Here is the
code. Very simple.
For every file that opens, I get a box that asks if I want to
overwrite the file. How do I force the answer to the popup to be yes?

#!c:/perl/bin/perl -w

use strict;
use warnings # prefer this over -w
use Win32::OLE;
$workdir = "c:\\temp\\GLNWI";

my $workdir = 'c:/temp/GLNWI';

....
for my $file_name (@gln_wi) {
chomp $file_name;
$file_name = "$workdir/$file_name";
print "file_name = $file_name\n";
$excel = new Win32::OLE ('Excel.Application', 'Quit');

# get already active Excelapplication or open new
my $excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$excel->{Visible} = 0;
$excel->Workbooks->Open("$file_name") or die ("Error: unable to
open document ", Win32::OLE->LastError());
$excel->Range("A1")->{Value} = "Rev A";
$excel->SaveAs('$file_name');

I don't think you want to use single quotes there.

Instead of using SaveAs, you might want to close the workbook with the
SaveChanges flag set:

$excel->Close(1);
undef $excel;

Very unnecessary.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top