[EMAIL PROTECTED] wrote on 03/16/2006 05:37:53 AM: > 1. OLE/Excel and worksheets ([EMAIL PROTECTED]) > 7. RE: OLE/Excel and worksheets (Brian Raven) > > ---------------------------------------------------------------------- > ------------------------------ > > Message: 7 > Date: Thu, 16 Mar 2006 10:29:46 -0000 > From: "Brian Raven" <[EMAIL PROTECTED]> > Subject: RE: OLE/Excel and worksheets > To: <[email protected]> > Message-ID: > <[EMAIL PROTECTED]> > Content-Type: text/plain; charset="us-ascii" > > [EMAIL PROTECTED] <> wrote: > > Since I'm still rather new, I was wondering if anyone with more > > experience than I could answer a question before i test it and > > possibly ruin data. > > If your testing risks ruining data, then you are probably not doing it > right. If you test on live data, rather than a copy then you are asking > for trouble. > Thank you. I have been using a copy, I'm just not sure what others using the computer may do and was told to be sure someone else could accidentally set it off while I make it without damaging anything, hence the constraint. > > > > On an existing workbook with two or more sheets, is it possible to > > select a specific sheet by name or create a new one: > > > > IE: i need to collect information daily and dump it into file.xls, > > would this code work to use the existing page for the day if it is > > there or make it and use it if not? > > > > my $ws = $report->Worksheets("$date - General"); > > > > if not, how would i go about testing for a particular sheet and > > create it if not? i assume creation would look something like > > > > my $ws = $workbook -> Worksheets(1); > > $ws -> { 'Name' } = "$date - General"; > > > > but have no idea how to test aside form checking for an error > > returned on the first. > > > > > > If you would like to see the full code, since there is a freeware > > program > > it uses, I have zipped them and placed them on the web at: > > > > http://s58.yousendit.com/d.aspx?id=3HAALXPD1QNS005WV01HL9JULR > > > > while most lines in the code are 80 characters or less, some are not, > > and > > there's the psinfo.exe, between the two i felt this would be the best > > way > > to "attach" the code for those who want to review it. in entirety > > before > > giving advice. > > I haven't looked at your code, but I have a couple of suggestions. > > General point, in case you didn't know already, the readily available > OLE documentation is pretty sketchy and is contained in the Visual Basic > Reference for the application concerned. Also, the OLE browser kindly > provided by Activestate can be quite useful. found that in my searching. this link is the only good ref i found unsigned. http://www.ngbdigital.com/perl_ole_excel.html the rest are all copies of the active state stuff signed "Jan Dubois" as the author. odd how pretty much anything good on this comes from one source. I hope that person is on this list. maybe i might get an authoritative answer on the topic since it seems like (s)he would know (considering the first name I'm not sure if I should address the person as he or she. I know a few ladies that use the name Jan as a short for Janet and I have a friend whose parents are from one of the European countries with a Slavic character set based language named Jan and from what I understand it is more common than bob or john is in the US) > > Collections (e.g. Worksheets) in OLE have a standard interface so a > general purpose subroutine can be used to locate an item by name. For > example, I use this. > > # Find an item whose name property matches. The match can be either a > # string or a regex. > sub find_item { > my $cont = shift; > my $match = shift; > my $found; > > if (ref($match) eq "Regexp") { > $found = sub { return $_[0] =~ /$match/; }; > } > else { > $found = sub { return $_[0] eq $match; }; > } > > for my $i (1..$cont->{Count}) { > my $item = $cont->Item($i); > return $item if $found->($item->{Name}); > } > return undef; > } > > It could possibly be made even more general by providing the attribute > name as a parameter. > > As far as creating a new worksheet goes, my guess from a brief look at > the doco would be the Add method on a Worksheets collection. For example > (untested, I just made this up, but it should give the general idea). > > # Return the worksheet with the specified name, creating it if > necessary. > sub get_worksheet { > my $workbook = shift; > my $wsname = shift; > > my $ws = find_item($workbook->Worksheets, $wsname); > return $ws if defined $ws; > > $workbook->Worksheets->Add; > $ws = $Workbook->{ActiveSheet}; > $ws->{Name} = $wsname; > return $ws; > } > > You will need to add error checking. > Will definitely try this. thank you for the suggestion! > HTH > > -- > Brian Raven -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
