smitty1624 has asked for the wisdom of the Perl Monks concerning the following question:

Looking for some help with the reason as to why my run time is taking about a minute when using Win32::OLE SaveAs. Does it have to do with the fact that the Fileformat is = xlTextPrinter?

# This script is used to run like a Excel Macro
#!/usr/bin/perl -w

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

#### File location
my $Filename = ('C:\xxxx\xxxx\xxx.xls');

#### Open Excel
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{SheetsInNewWorkbook} = 0;
my $Book1 = $Excel->Workbooks->Open($Filename) || die("Could not open file!");
my $Sheet1 = $Book1->Worksheets(1);

#### Column A
$Sheet1->Columns("A:A")->{ColumnWidth} = "25";
$Sheet1->Columns("A:A")->{HorizontalAlignment} = xlLeft;
$Sheet1->Columns("A:A")->{VerticalAlignment} = xlBottom;

#### Column B
$Sheet1->Columns("B:B")->{ColumnWidth} = "5";
$Sheet1->Columns("B:B")->{HorizontalAlignment} = xlLeft;
$Sheet1->Columns("B:B")->{VerticalAlignment} = xlBottom;

#### Column C
$Sheet1->Columns("C:C")->{ColumnWidth} = "25";
$Sheet1->Columns("C:C")->{HorizontalAlignment} = xlLeft;
$Sheet1->Columns("C:C")->{VerticalAlignment} = xlBottom;

#### Column D
$Sheet1->Columns("D:D")->{NumberFormat} = "0000000.00";
$Sheet1->Columns("D:D")->{ColumnWidth} = "10";
$Sheet1->Columns("D:D")->{HorizontalAlignment} = xlRight;
$Sheet1->Columns("D:D")->{VerticalAlignment} = xlBottom;

#### Column E
$Sheet1->Columns("E:E")->{ColumnWidth} = "1";
$Sheet1->Columns("E:E")->{HorizontalAlignment} = xlLeft;
$Sheet1->Columns("E:E")->{VerticalAlignment} = xlBottom;

#### Column F
$Sheet1->Columns("F:F")->{ColumnWidth} = "5";
$Sheet1->Columns("F:F")->{HorizontalAlignment} = xlLeft;
$Sheet1->Columns("F:F")->{VerticalAlignment} = xlBottom;

#### Column G
$Sheet1->Columns("G:G")->{ColumnWidth} = "40";
$Sheet1->Columns("G:G")->{HorizontalAlignment} = xlLeft;
$Sheet1->Columns("G:G")->{VerticalAlignment} = xlBottom;

#### Add X to Last Column
my $LastRow = $Sheet1->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows})->{Row};
my $LastCol = $Sheet1->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByColumns})->{Column};
$mylastcol2 = 'a';
for (my $m=1; $m < $LastCol + 1; $m++) {$mylastcol2++;}

my $Range1 = ("$mylastcol2"."1");
my $Range2 = ("$mylastcol2"."$LastRow");
my $Range = ("$Range1".":"."$Range2");

$Book1 -> ActiveSheet -> Range($Range)->{'Value'} = "X";

#### Save As .prn file
$Book1->SaveAs({Filename =>'C:\xxxx\xxxx\xxx.prn',
FileFormat => xlTextPrinter});

#### Close
$Book1->Close;
$Excel->Quit;

Replies are listed 'Best First'.
Re: WIN32::OLE Excel Save As
by kyle (Abbot) on May 02, 2007 at 18:12 UTC

    You might have a look at Devel::DProf or Devel::Profiler. You could also sprinkle Time::HiRes in the code and see where it's taking the most time. 

    use Time::HiRes qw( gettimeofday tv_interval );

my $start_time = [gettimeofday];
# work hard
printf "hard work took %.2f seconds\n",
       tv_interval( $start_time );

    [download]
[reply]
[d/l]
      I have added the Time::HiRes to my script and it printed out "work hard took 0.00 seconds".

      Could the delay that I am seeing be cause be calling the Excel app?

      When I change the file format to Save As a .csv file, it runs in seconds.

      Looking at the Devel::DProf & Devel::Profiler
[reply]

        When I change the file format to Save As a .csv file, it runs in seconds.

        Well, that sounds pretty conclusive to me. Figuring out why Excel takes so long for the file you're giving it may be a pain, and I doubt that examining your own code further will help a whole lot. Time to ask someone who knows Excel somewhat better.

[reply]
Re: WIN32::OLE Excel Save As
by Anonymous Monk on Mar 25, 2015 at 05:30 UTC

    sir while i m trying to use Save As it gives me error

    i use all modules that used in your code but still not working

    i want to save my .xls file to .xlsx file

    code:
$book->SaveAs({Filename =>'D:\bharat\xxx.xls',FileFormat => xlExcel3})
+;


error/warning message:
Unable to get the SaveAs property of the Workbook class

Win32::OLE(0.1712) error 0x800a03ec
    in METHOD/PROPERTYGET "SaveAs" at C:\Users\training\Desktop\New\ts
+t.pl line
57.

    [download]

    while it is working if i use this line 

    $book->SaveAs( 'D:\bharat\test.xls');

    [download]
[reply]
[d/l]
[select]

      Need small change in filename

      just put the filename outside the curlybrace

      Example :
$book->SaveAs( PATH/filenm.xlsx,{FileFormat => xlWorkbookDefault});

      [download]

      Works well with my ceode.. best of luck

[reply]
[d/l]

Back to Seekers of Perl Wisdom