[EMAIL PROTECTED] wrote on 03/17/2006 03:05:18
PM:
> Today's Topics:
>
> 4. OLE/M$ Excel and finding sheets ([EMAIL PROTECTED])
> 5. RE: OLE/M$ Excel and finding sheets (Brian Raven)
> ----------------------------------------------------------------------
> ------------------------------
>
> Message: 5
> Date: Fri, 17 Mar 2006 17:12:36 -0000
> From: "Brian Raven" <[EMAIL PROTECTED]>
> Subject: RE: OLE/M$ Excel and finding sheets
> To: <[email protected]>
> Message-ID:
> <[EMAIL PROTECTED]>
> Content-Type: text/plain; charset="us-ascii"
>
> [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);
>
> Just shift will do.
>
> > 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");
>
> Useless quoting. See 'perldoc -q quoting'
>
> >
> > my $ws = $report->Worksheets("$dessheet");
>
> Useless quoting again, but locating a worksheet by name does seem to
> work. Colour me mildly surprised.
I was surprised two. I found that I would get errors on names not done as
variables without '' or "" around them when using strict, hence the habit
of putting them in there.
>
> >
> > if($ws){
> > $fcell = $ws -> Range("A1") -> {'Value'};
> >
> > print "\$ws: $ws\n\t\$fcell: $fcell\n";
> > }else{
> > $workbook = $Excel -> Workbooks -> Add();
>
> This adds a workbook, I thought you wanted to add a worksheet. Shouldn't
> that be:
> $report->Worksheets->Add;
> I don't know that it returns the new worksheet.
I did find out that was the case, and that Worksheets has Add as well =o)
>
> > $ws = $workbook -> Worksheets(1);
>
> The added worksheet is made the active worksheet, so that should
> probably be
> $ws = $report->{ActiveSheet}
>
> > $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 }
>
> $report->Save();
> $Excel->Quit();
>
> HTH
>
> --
> Brian Raven
I've gotten a bit further on the full script, but still have an issue
where it doesnt save right, and if i prevent the "die on error" then it
does not quit right.
full code and psinfo @:
http://rapidshare.de/files/15758842/compinfo.zip.html
I have not figured those two out yet and have been avoiding going directly
at them since they are in the OLE script that they are erroring. I haven't
played in the source of that module ever. Kinda worried if I try to debug
someone else's code I'll corrupt something unknowingly. I plan on looking
up the author and his/her email address when I am sure that is all that is
left and sending an email letting them know so that person may look into
it.
At this point the content's layout is still a bit in the air, as it the
exact content. it seems to change slightly every time I talk to my boss.
If you or anyone else would like to look at the save/quit error and help,
it would be immensely appreciated. if the originally or current
maintenance author is on the list and would like to help i would be
flattered.
The error is:
retrying default method at /PerlApp/Win32/OLE/Lite.pm line 156.
Win32::OLE(0.1403) error 0x8002000b: "Invalid index"
in METHOD/PROPERTYGET "" at compinfo.pl line 262
line 262 is: $Excel -> Workbooks -> Save(); # save file
and the next one closes out Excel. This is right after the loop inside of
the if/else to handle pre-existing files and placing the gathered
information into the spreadsheet. This happens both when it is called on a
new sheet that "save as" has successfully completed on and when there was
no "save as" call because the file was previously existing. (I have tried
monitoring and closing out with save as the loop ends. It makes no
difference.)
-Josh
-----------------------------------------
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
