[EMAIL PROTECTED] wrote:
For those interested I decided to play around with simply finding a
specific sheet and see what happens if i started with the most basic
calling by name. the following is my code/errors. On the two times that it
asked if there was an added sheet, it created a new workbook instead and
did not create a named sheet.
At least this gives a way to get a sheet already present.
I'm wondering, has anyone started putting together a sheet on OLE or
OLE/Excel functions and syntaxes? Does anyone know of one?
-Josh
-----code-----
#! /usr/bin/perl
use strict;
use Win32::OLE qw(in with); # use base OLE
use Win32::OLE::Const 'Microsoft Excel'; # use OLE/Excel
# $Win32::OLE::Warn = 3; # die on errors...
my $dessheet=shift(@ARGV);
my $fcell='';
my $workbook='W:/lab reports/tester.xls';
unless(-e $workbook){
print "cant find the workbook\n";
}else{
my $Excel = Win32::OLE -> GetActiveObject('Excel.Application')
|| Win32::OLE -> new('Excel.Application', 'Quit');
$Excel -> {'Visible'} = 1;
my $report = $Excel->Workbooks->Open("$workbook");
my $ws = $report->Worksheets("$dessheet");
if($ws){
$fcell = $ws -> Range("A1") -> {'Value'};
print "\$ws: $ws\n\t\$fcell: $fcell\n";
}else{
$workbook = $Excel -> Workbooks -> Add();
$ws = $workbook -> Worksheets(1);
$ws -> { 'Name' } = "new sheet";
$ws -> Range("A2") -> {'Value'} = "was i successful?";
print "added sheet?\n";
}
$Excel -> Workbooks -> Save(); # save file
$Excel -> Workbooks -> Quit(); # leave excel
}
----errors------
W:\lab reports>perl sheettest.pl test
---snipped---
Problem is methods, objects and classes are mixed up. This might help -
#!perl -w
use strict;
use warnings;
use Win32::OLE;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
Win32::OLE->new('Excel.Application'); #, 'Quit');
# Use Quit in script only when debugging is done or]
# you might not see what went wrong in Excel..
$Excel->{Visible} = 1;
#No forward slashes - they will cause failure with Open
my $reportFile = "c:\\test\\tester.xls";
my $Book = $Excel->Workbooks->Open($reportFile);
if(workSheetExists('Added blindly', $Book))
{
$Book->Sheets('Added blindly')->Range("A1")->{Value} =
"We've been here before";
}
else
{
# Add a worksheet - position not set
$Book->Worksheets->Add()->{NAME} = 'Added blindly';
}
if(workSheetExists('End Sheet', $Book))
{
#Make sheet active
$Book->Sheets('End Sheet')->Range("A1")->{Value} =
"Been here, too...";
}
else
{
# Now set up to add new sheet at end..
my $sheetCount = $Book->Worksheets->{Count};
my $lastSheet = $Book->Worksheets($sheetCount);
$Book->Worksheets->Add({After => $lastSheet})->{NAME} = 'End Sheet';
}
# Save, close quit methods - Close for illustration only
$Book->Save;
$Book->Close;
$Excel->Quit; #quit comment at top also applies here...
sub workSheetExists
{
my $tst = shift;
my $wkbook = shift;
for my $a(1 .. $wkbook->Worksheets->Count)
{
if ($wkbook->Worksheets($a)->Name eq $tst)
{
return 1;
}
}
return 0;
}
HTH - Lynn.
_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
