Lynn. Rickards <> wrote:
> [EMAIL PROTECTED] wrote:
>> Mr. Rickards, MR. Raven et al-
>>
>> I still get some errors and i'm not sure why on the OLE ones. It is
>> not saving/closing on either version, operating on previously
>> existing nore building new.
>
> I'm going to try and go thru the bughunting process, with references
> to pertinent documentation. This OLE stuff is just like riding a
> bike: Once you get the hang of it, you can get real good at it, yet
> still run the risk of falling off and breaking your neck ;-)
>
>>
>> operating on existing:
>>
>> Microsoft Windows [Version 5.2.3790]
>> (C) Copyright 1985-2003 Microsoft Corp.
>>
>> e:\>cd "lab reports"
>>
>> e:\lab reports>cd test45
>>
>> e:\lab reports\test45>cataloge.bat
>>
>> e:\lab reports\test45>rem this batch is for cataloguing computers
>> using two other "helpers"
>>
>> e:\lab reports\test45>rem "helpers" are psinfo and compinfo
>>
>> e:\lab reports\test45>rem information section is over, turning echo
>> off. testing 'Sheet1' against 'General - 2006-Mar-20'
>>
>
> --repeated output messages snipped--
>
> First suggestion: Test code requiring use of unfamiliar methods a
> piece at a time on test or hardcoded data, rather than try to develop
> the whole application in one go ;-)
>
>
>> OLE exception from "Microsoft Excel":
>>
>> Unable to get the Add property of the Sheets class
>>
>> Win32::OLE(0.1403) error 0x800a03ec
>> in METHOD/PROPERTYGET "Add" at compinfo.pl line 171 e:\lab
>> reports\test45>
>
> When you get a message like this it really is telling you something
> useful: You have attempted to use a method inappropriately on a
> class...
>
> It also provides the class name - 'Sheets'
>
> Documentation is built right into Excel, as long as you chose to
> install the Visual Basic option:
>
> Tools->Macros->Visual Basic Editor.
>
> Mouse right-click over main project edit pane to get menu->Object
> Browser
>
> At the top of the Object Browser there are two dropdown entry
> widgets. Top one is Project/Library, select Excel to filter on only
> Excel objects.
>
> Below that is the 'search' widget. Type 'add' and hit the search
> button to the right.
>
> Now in the 'Search Results' frame you have everything pertaining to
> Add. Scroll down to find 'Save' in the member column.
>
> Select that line, and in what I'll call the helper frame, you see:
>
> Function Add([Before], [After], [Count], [Type]) As Object
> Member of Excel.Worksheets
>
>
>>
>> ------------------------------------------------------------
>> line 171: $ws1 = $Excel -> Worksheets -> Add({After => $ls}); #
>> add as last sheet
>
> So, after reading the error message and your line above, you can see
> that your basic syntax here is good. There must be something wrong
> with the content. What is $ls?
>>
>>
>> surrounding area (161-181)
>> my $ws3t="Applications - $date";
>>
>> # check for sheets
>> my $ws1n = &ExistingWorksheet($ws1t,$report);
>> my $ws2n = &ExistingWorksheet($ws2t,$report);
>> my $ws3n = &ExistingWorksheet($ws3t,$report);
>>
>> # make the sheets for today if they do not exist
>> unless($ws1n){
>> my $ls = $report->Worksheets->{Count}; # get last sheet
>> $ws1 = $Excel -> Worksheets -> Add({After => $ls}); # add as last
>
> Check the example I provided the other day:
>
> my $sheetCount = $Book->Worksheets->{Count}; my $lastSheet =
> $Book->Worksheets($sheetCount); $Book->Worksheets->Add({After =>
> $lastSheet})->{NAME} = 'End Sheet';
>
> So...$ls contains simply the number of sheets in the book, which is
> why the Add routine has trouble with it, when it expects an object
> reference to the last sheet with the 'After' parameter.
>
> You omitted the call to get that object ($lastSheet in my example)
>
>> sheet
>> $ws1 -> {'Name'} = "General - $date";
>> }else{
>> $ws1 = $report -> Worksheets($ws1n);
>> }
>> unless($ws2n){
>
> -- Lots of script output errors or warnings snipped though it looks
> like data returned from system calls may be suspect and needs serious
> validation...
>
> Two quite separate OLE errors here. First points to line 283 which
> you don't provide, but suggest you investigate documentation, script
> syntax and value of variables used - note in 'METHOD/PROPERTYGET ""
> at compinfo.pl' it looks like you have an empty variable in there
> (the empty "" quotes)
>
>> The system cannot find the file specified.
>> The system cannot find the file specified.
>> 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 283 testing 'General
>> - 2006-Mar-20' against 'General - 2006-Mar-20'
>>
>> testing 'General - 2006-Mar-20' against 'Hotfixes - 2006-Mar-20'
>>
>> testing 'Hotfixes - 2006-Mar-20' against 'Hotfixes - 2006-Mar-20'
>>
>> testing 'General - 2006-Mar-20' against 'Applications - 2006-Mar-20'
>>
>> testing 'Hotfixes - 2006-Mar-20' against 'Applications - 2006-Mar-20'
>>
>> testing 'Applications - 2006-Mar-20' against 'Applications -
>> 2006-Mar-20'
>>
>> Use of uninitialized value in pattern match (m//) at compinfo.pl
>> line 197. retrying default method at /PerlApp/Win32/OLE/Lite.pm line
>> 156. Win32::OLE(0.1403) error 0x8001010a: "The message filter
>> indicated
>> that the application is busy"
>> in METHOD/PROPERTYGET "" at compinfo.pl line 320 e:\lab
>> reports\test45>
>>
>
> Investigate and solve the 'uninitialized variable' message from
> perl first, or all bets are off on why Excel is reporting busy..
I would just like to reinforce Lynn's points. As I said in an earlier
post, some trial and error is often necessary given the nature of the
documentation available, and as Lynn says, it is easier done in small
pieces that a whole application.
>
>> 197: $ncell = $ws1 -> Range("A$row") -> {'Value'};
>> i think 156 and 320 are ole.
>>
>> code and such again:
>> http://rapidshare.de/files/16004488/compinfo.zip.html
>>
>> Mr. Raven, i'll send the zip directly to you since you cant get it.
>> I know that actrive state scrubs it.
I would much prefer that you posted a small self contained script to
this list that illustrated a problem you are having. As an example a
script to test a subroutine that finds a named worksheet or adds it if
it doesn't exist might look like this.
use strict;
use warnings;
use Win32::OLE qw(in);
my $workbook='C:/ptest/test.xls';
die "cant find the workbook\n" unless -e $workbook;
my $app = Win32::OLE -> GetActiveObject('Excel.Application')
|| Win32::OLE -> new('Excel.Application', 'Quit');
$app -> {Visible} = 1;
my $wb = $app->Workbooks->Open($workbook);
my $ws = find_or_create_ws($wb, "New WS");
print "WS name is ", $ws->{Name}, "\n";
$wb->Save();
$app->Quit();
sub find_or_create_ws {
my ($wb, $name) = @_;
my @sheets = in($wb->Worksheets);
my @matches = grep {$_->{Name} eq $name} @sheets;
return $matches[0] if @matches == 1;
die "Multiple matches for $name?\n" if @matches > 1;
# Add new worksheet after last
print "Adding worksheet $name\n";
my $lastws = $sheets[-1];
my $newws = $wb->Worksheets->Add({After => $lastws});
$newws->{Name} = $name;
return $newws;
}
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
