[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.
>
> 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.
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.
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
