[EMAIL PROTECTED] <> wrote: > [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.)
Perhaps I didn't make it clear enough in my post. If you check the documentation you will find that Save is not a valid method to call on a Workbooks object (i.e. a collection), hence my suggestion of "$report->Save();", i.e. calling save on the workbook that you have just been editing. Similarly, neither is the Quit method valid for a Workbooks object, it should properly be called on the application object, in case you haven't fixed that line of code either. Sorry, I can't look at your code via the link you posted as our corporate firewall blocks P2P servers. HTH -- Brian Raven ================================= Atos Euronext Market Solutions Disclaimer ================================= The information contained in this e-mail is confidential and solely for the intended addressee(s). Unauthorised reproduction, disclosure, modification, and/or distribution of this email may be unlawful. If you have received this email in error, please notify the sender immediately and delete it from your system. The views expressed in this message do not necessarily reflect those of Atos Euronext Market Solutions. L'information contenue dans cet e-mail est confidentielle et uniquement destinee a la (aux) personnes a laquelle (auxquelle(s)) elle est adressee. Toute copie, publication ou diffusion de cet email est interdite. Si cet e-mail vous parvient par erreur, nous vous prions de bien vouloir prevenir l'expediteur immediatement et d'effacer le e-mail et annexes jointes de votre systeme. Le contenu de ce message electronique ne represente pas necessairement la position ou le point de vue d'Atos Euronext Market Solutions. _______________________________________________ ActivePerl mailing list [email protected] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
