Why is perl saving two copies of Excel spreadsheet?

Question

This is similar to A copy of Excel Addin is created in My Documents after saving, except that I'm working with Perl instead of VBA, and xls files instead of xlsm, and the negative impact of the behavior is different.

I've inherited a Perl script (Perl 5.8.8) that is running on Windows 2003 Server as SYSTEM. After copying an Excel 2003 template file to a unique, fully defined path location, it opens the unique file in Excel using OLE, edits the file, saves the file, and closes the file. What results is the edited file being saved both in the correct, fully-defined path location, and also in the Default User profile's Documents folder.

This causes thousands of these files to accumulate on the C: drive, as every new admin to be hired gets a copy in his Documents folder.

Adding the code that sets the value of $OUT:

if (!$db->Sql("EXEC GetDetails 'name'"))
{
    while ($db->FetchRow()>0)
    {
        @DataIn = $db->Data();
        $name = $DataIn[0];
        $IN = $DataIn[1];
        $OUT = $DataIn[2];
        opendir(DIR,"$OUT") || die "$OUT directory does not exist $!\n";

        #... loop of proprietary code
            #...
            @Completed = $db1->Data();
            #...

            &formatExcelReport  #The code that I previously posted

            #...
            # more proprietary code
        # end of loop

    } #end of while
}#end of if

The code I originally posted:

# Initialize Excel object
eval {Win32::OLE->new('Excel.Application', 'Quit')};
eval {$Excel = Win32::OLE->GetActiveObject('Excel.Application')};
unless (defined $Excel)
{
    $Excel = Win32::OLE->GetActiveObject('Excel.Application')
             || Win32::OLE->new('Excel.Application', 'Quit');
}
$infiles = "Report_Template.xls";
$infiles = $OUT."/".$infiles;
$db6->Sql("EXEC FormatResults '".$Completed[0]."','".$Completed[1]."'");
$row = 2;
$fileName = $Completed[0]."_".$Completed[1];
$uniquefile = $fileName.$printdate.".xls";

# $OUT is a fully defined path on the E: drive
$reportfile = "$OUT"."\\".$uniquefile;
copy($infiles,$reportfile);
$Book = $Excel->Workbooks->Open("$reportfile");
$sheetnum = 1;
my $Sheet = $Book->Worksheets($sheetnum);

# Set Headers
$Header = $Sheet->PageSetup->{'CenterHeader'};
$Header = $Header." Results Test Code: ".$Completed[0]." Worksheet: ".$Completed[1]." Date: ".$headerdate;
$Sheet->PageSetup->{'CenterHeader'}= $Header;

# More file editing
# ...

$Book->Save();
$Book->Close(0);
Win32::OLE->new('Excel.Application', 'Quit');

Is the root of this problem the Save() command? Should I be using SaveAs() instead?

Any other feedback about how Excel is being used welcome, as well.

Thanks!

Answer 1

I don't see what causes this behavior, but here are a few things to try.

• The template and the file it is copied to have names

    $infiles = $OUT."/".$infiles;
    $reportfile = "$OUT"."\\".$uniquefile;
  

Use the same separator.

• Try to suppress some possible setting dictating that another copy be made. Perhaphs

    $Excel->Application->{CreateBackup} = 0;
  

However, this may not be the correct property -- search the VB or Excel documentation for properties that may result in Excel saving an extra copy. (It needn't be "backup".)

• Try to create a new file and use SaveAs, as a test to see whether you get two files again. The template copying may be setting it off to Save an extra copy (even though I don't see how). I'd say it's either that, or some general setting that need be turned off.

---

The rest is the original post, about using SaveAs, whereby I thought that a new file is created

You would use SaveAs to write a new file. See saveas in MSDN library

Saves changes to the workbook in a different file.

Using the save method may result in saving two files fro some reason, as noted in the answer by Borodin. This page also advises to use SaveAs for a new file

The first time you save a workbook, use the SaveAs method to specify a name for the file.

Once you change to using SaveAs there should be a confirmation dialog to deal with. If you want to suppress that you can set a property, with one (or either?) of

$Excel->Application->{DisplayAlerts} = 0;
# or
$Excel->{DisplayAlerts} = 0;

For a number of options, including backups for example, see the Chapter on OLE automation in PERL in a Nutshell.

---

A note on some other resources. There is a cookbook of sorts in this post on perlmonks. A listing of various operations is given in this SO post.

Finally, I don't know how deep the reasons for using OLE are but if it is only about writing some Excel files there are other modules. For example the very well regarded Spreadsheet::WriteExcel and Excel-Writer-XLSX.

Answer 2

That's very strange Perl code. eval without checking $@ afterwards is just wrong -- you need to know if a step of your code has failed for the following steps to make sense

It looks like the problem is in your call to copy($infiles, $reportfile). That will save one copy of the file, while $Book->Save and $Book->Close will save another