Note the difference between the line in my example:
if ($wkbook->Worksheets($a)->Name eq $tst)
and yours, which fails...
if($ewb -> Worksheets($a) -> { 'Name' } eq $test)
- Lynn.
> -------Original Message-------
> From: [EMAIL PROTECTED]
> Subject: Re: OLE/M$ Excel and finding sheets
> Sent: Mar 20 '06 10:26
>
> Mr Rickards et al-
>
> I made a checker based on yours when the small function didn't work
> plugged into the full script.
>
> i get these errors
>
> e:\lab reports\test45>rem information section is over, turning echo off.
> Win32::OLE(0.1403) error 0x80020005: "Type mismatch"
> in METHOD/PROPERTYGET "Worksheets" argument 1 at compinfo.pl line 400
> Win32::OLE(0.1403) error 0x80020005: "Type mismatch"
> in METHOD/PROPERTYGET "Worksheets" argument 1 at compinfo.pl line 400
> e:\lab reports\test45>
>
> I'm not sure what is causing this.
> the subroutine contains line 400 (the equality check line) and is
>
> ---code---
> sub ExistingWorksheet{
> # this looks to see if a worksheet exists
> my $test = $_[0]; # name to test for
> my $ewb = $_[1]; # Excel WorkBook to look in
>
> for my $sn (1 .. $ewb -> Worksheets -> Count){
> # for each sheet in the book
> if($ewb -> Worksheets($a) -> { 'Name' } eq $test){
> # if they are the same return true (postivie #) as the sheet number
> return $a;
> }
> }
>
> return 0; # if we get to this line it doesn't exist, return false
> }
> ---code---
>
> due to use of strict, name cannot be a bareword
>
> full code w/psinfo & test report base file in link
> http://rapidshare.de/files/15980042/compinfo.zip.html
>
> -Josh
>
> "Lynn. Rickards" <[EMAIL PROTECTED]> wrote on 03/17/2006 10:48:57 PM:
>
> > [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.
>
>
> -----------------------------------------
> PLEASE NOTE:
> SeaChange International headquarters in Maynard, MA is moving!
> Effective March 1, 2006, our new headquarters address will be:
>
> SeaChange International
> 50 Nagog Park
> Acton, MA 01720 USA
>
> All telephone numbers remain the same:
> Main Corporate Telephone: 978-897-0100
> Customer Service Telephone: 978-897-7300
>
>
_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
