Excel in Perl VIA OLE -- saving workbook(s)

[Guest]

in the following code, line 232 (the save line that is called after the loop)
errors and kills the program. this is causing another error later on when i
call this again.

Does anyone know why this is or have suggestions on how to fix it?

if(-e "$labrep"){
#we are just adding to it, so open it
my $report = $Excel->Workbooks->Open("$labrep");
# my $ws = $report->Worksheets("$date - General");
my $ws = $report->Worksheets(1);

# start row counter
my $row=2;
# set row counter to continue at the end of what's there
my $ncell = $ws -> Range("A$row") -> {'Value'};
while($ncell =~ m/\w+/i){
$row++; # increment row & V- grab next row's data -V
$ncell = $ws -> Range("A$row") -> {'Value'};
}

# we need to get the information & parse it into the excel file for each
node
foreach my $node (@comps){
# for each node we check the information returned
my $ldate = localtime();
&rep("Examining information gathered from node $node @ $ldate\n", $verb);

# a sub routine defined later in the script is used for simplicity
&populate($node,$row,$ws);

# end the loop increasing the row number
$row++;
}
}else{
# we have to create it, including make the first row
$Excel -> { 'SheetsInNewWorkBook' } = 1;
my $workbook = $Excel -> Workbooks -> Add();
my $ws = $workbook -> Worksheets(1);
$ws -> { 'Name' } = "$date - General";

# set first row titles
$ws -> Cells(1, "A") -> {'Value'} = "Node";
$ws -> Cells(1, "B") -> {'Value'} = "NAV";
$ws -> Cells(1, "C") -> {'Value'} = "Alarms";
$ws -> Cells(1, "D") -> {'Value'} = "SNMP";
$ws -> Cells(1, "E") -> {'Value'} = "Uptime";
$ws -> Cells(1, "F") -> {'Value'} = "Kernel Version";
$ws -> Cells(1, "G") -> {'Value'} = "Product Type";
$ws -> Cells(1, "H") -> {'Value'} = "Product Version";
$ws -> Cells(1, "I") -> {'Value'} = "Service Pack";
$ws -> Cells(1, "J") -> {'Value'} = "Kernel Build Number";
$ws -> Cells(1, "K") -> {'Value'} = "Registered Organization";
$ws -> Cells(1, "L") -> {'Value'} = "Registered Owner";
$ws -> Cells(1, "M") -> {'Value'} = "Install Date";
$ws -> Cells(1, "N") -> {'Value'} = "Activation Status";
$ws -> Cells(1, "O") -> {'Value'} = "IE Version";
$ws -> Cells(1, "P") -> {'Value'} = "System Root";
$ws -> Cells(1, "Q") -> {'Value'} = "Processors";
$ws -> Cells(1, "R") -> {'Value'} = "Processor Speed";
$ws -> Cells(1, "S") -> {'Value'} = "Processor Type";
$ws -> Cells(1, "T") -> {'Value'} = "Physical Memory";
$ws -> Cells(1, "U") -> {'Value'} = "Installed OS Hotfixes";
$ws -> Cells(1, "V") -> {'Value'} = "Other Applications";
$ws -> Columns("A:V") -> AutoFit();

# start row counter
my $row=2;

foreach my $node (@comps){
# for each node we check the information returned
my $ldate = localtime();
&rep("Examining information gathered from node $node @ $ldate\n", $verb);

# a sub routine defined later in the script is used for simplicity
&populate($node,$row,$ws);

# end the loop increasing the row number
$row++;
}

$workbook -> SaveAs($labrep); # save active sheet
}

# save and exit
$Excel -> Workbooks -> Save(); # save file
$Excel -> Workbooks -> Quit(); # leave excel
my $et=locatime();
&rep("program completed at $et.",$verb); # wrap up log
close LOG; # close log

 

[Guest]

Josh,

Obviously you need to figure out what your first error is and fix that.

I’m assuming, since you’re using Perl, that you are trying to run automated
Excel. Excel was designed to run interactively so if you haven’t read
http://support.microsoft.com/default.aspx?scid=kb;LN;257757 it may be helpful
to do that. One thing to check is your Task Manager’s Process list to
confirm that Excel is closing when you told it to. Just because you called
Quit() doesn’t mean that the process was terminated. And if you have too
many Excel processes active Excel will eventually be unable to load.

If you could add any more insight to the problem, like a specific error
condition or codes, it would make it easier for us to help you.

Good luck,

Kim

 

[Guest]

Josh,

Obviously you need to figure out what your first error is and fix that.

I’m assuming, since you’re using Perl, that you are trying to run automated
Excel. Excel was designed to run interactively so if you haven’t read
http://support.microsoft.com/default.aspx?scid=kb;LN;257757 it may be helpful
to do that. One thing to check is your Task Manager’s Process list to
confirm that Excel is closing when you told it to. Just because you called
Quit() doesn’t mean that the process was terminated. And if you have too
many Excel processes active Excel will eventually be unable to load.

If you could add any more insight to the problem, like a specific error
condition or codes, it would make it easier for us to help you.

Good luck,

Kim

--
digipede - Many legs make light work.
Grid computing for the real world.
http://www.digipede.net
http://krgreenlee.blogspot.net

thank you. it turned out to be the way the call to save is done. it is
incorrect there. once that was fixed it worked, aside from when other things
were running and overloading the cpu