Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

PROPERTYPUT error in Win32::OLE

by Anonymous Monk
on Jun 13, 2005 at 10:05 UTC ( #466088=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am getting these errors;
Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 134 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 135 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 130 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 143 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 129 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 147 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 171 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 128 Win32::OLE(0.1702) error 0x80020009: "Exception occurred" in PROPERTYPUT "Value" at C:\Perl\Test\im.pl line 134
Which corresponds to this bit of code!
$Sheet_tmpl->Cells($trow,4)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'RTO'); $Sheet_tmpl->Cells($trow,5)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'RPO'); $Sheet_tmpl->Cells($trow,6)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'SLA'); $Sheet_tmpl->Cells($trow,7)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'Host Name'); $Sheet_tmpl->Cells($trow,8)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'Location'); $Sheet_tmpl->Cells($trow,9)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'SAN'); $Sheet_tmpl->Cells($trow,10)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'NAS'); $Sheet_tmpl->Cells($trow,11)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'DAS'); $Sheet_tmpl->Cells($trow,12)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Current Tier'); $Sheet_tmpl->Cells($trow,13)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Allocated GB'); $Sheet_tmpl->Cells($trow,14)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Actual GB'); $Sheet_tmpl->Cells($trow,15)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Storage Raw (GB)'); $Sheet_tmpl->Cells($trow,16)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Storage Growth (% per year)'); $Sheet_tmpl->Cells($trow,17)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Application Type');
Any idea how can I get rid of these errors, and why did they occure? Thanks

Comment on PROPERTYPUT error in Win32::OLE
Select or Download Code
Replies are listed 'Best First'.
Re: PROPERTYPUT error in Win32::OLE
by guha (Priest) on Jun 13, 2005 at 11:00 UTC

    Difficult to tell without the actual code, but I wonder what value $trow has and if the corresponds to a cell in $Sheet_tmpl.

      I see, I will print out the values of $trow, which is just a pointer to the excel row.

      Here is the full code
      #! c:/perl/bin/perl.exe # good working version use strict; use Win32; use Win32::OLE; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use time::localtime; use Tk; use Tk::ProgressBar; my $percent_done=0; my $mw = MainWindow->new(-title => 'Processing Excel Extraction'); my $Progress = $mw->ProgressBar( -width => 30, -from => 0, -to => 100, -blocks => 50, -colors => [0,'blue',100,'blue'], -variable => \$percent_done )->pack(-fill => 'x'); $Win32::OLE::Warn =2; my $excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application'); $excel->{Visible} = 1; my $Book_tmpl = $excel->Workbooks->Add; my $Sheet_tmpl = $Book_tmpl->Worksheets(1); $Sheet_tmpl->{Name}="Template Results"; $Sheet_tmpl->Cells(1,1)->{Value}="Application"; $Sheet_tmpl->Cells(1,2)->{Value}="Integrity"; $Sheet_tmpl->Cells(1,3)->{Value}="Availability"; $Sheet_tmpl->Cells(1,4)->{Value}="RTO"; $Sheet_tmpl->Cells(1,5)->{Value}="RPO"; $Sheet_tmpl->Cells(1,6)->{Value}="SLA"; $Sheet_tmpl->Cells(1,7)->{Value}="Host Name"; $Sheet_tmpl->Cells(1,8)->{Value}="Location"; $Sheet_tmpl->Cells(1,9)->{Value}="SAN"; $Sheet_tmpl->Cells(1,10)->{Value}="NAS"; $Sheet_tmpl->Cells(1,11)->{Value}="DAS"; $Sheet_tmpl->Cells(1,12)->{Value}="Current Tier"; $Sheet_tmpl->Cells(1,13)->{Value}="Allocated GB"; $Sheet_tmpl->Cells(1,14)->{Value}="Actual GB"; $Sheet_tmpl->Cells(1,15)->{Value}="Storage Raw (GB)"; $Sheet_tmpl->Cells(1,16)->{Value}="Storage Growth (% per year)"; $Sheet_tmpl->Cells(1,17)->{Value}="Application Type"; $Sheet_tmpl->Cells(1,18)->{Value}="Business Unit"; $Sheet_tmpl->Cells(1,19)->{Value}="Business Unit Manager"; $Sheet_tmpl->Cells(1,20)->{Value}="Support Person"; $Sheet_tmpl->Cells(1,21)->{Value}="Relationship Manager"; $Sheet_tmpl->Cells(1,22)->{Value}="Country"; $Sheet_tmpl->Cells(1,23)->{Value}="OLA"; $Sheet_tmpl->Cells(1,24)->{Value}="Confidentiality"; $Sheet_tmpl->Cells(1,25)->{Value}="Server Role"; $Sheet_tmpl->Cells(1,26)->{Value}="Platform"; $Sheet_tmpl->Cells(1,27)->{Value}="HBA Card"; $Sheet_tmpl->Cells(1,28)->{Value}="HBA Card Type"; $Sheet_tmpl->Cells(1,29)->{Value}="HBA Card Total"; $Sheet_tmpl->Cells(1,30)->{Value}="LUNs Available"; $Sheet_tmpl->Cells(1,31)->{Value}="Number of Server Processors"; $Sheet_tmpl->Cells(1,32)->{Value}="Server Memory"; $Sheet_tmpl->Cells(1,33)->{Value}="Server Type"; $Sheet_tmpl->Cells(1,34)->{Value}="Frame"; $Sheet_tmpl->Cells(1,35)->{Value}="I/O Throughput (p/sec)"; $Sheet_tmpl->Cells(1,36)->{Value}="Data Protection Method"; $Sheet_tmpl->Cells(1,37)->{Value}="Number of Replicated Copies"; $Sheet_tmpl->Cells(1,38)->{Value}="Location of Replicated Copies"; $Sheet_tmpl->Cells(1,39)->{Value}="Backup"; $Sheet_tmpl->Cells(1,40)->{Value}="Backup Frequency"; $Sheet_tmpl->Cells(1,41)->{Value}="Backup Type"; $Sheet_tmpl->Cells(1,42)->{Value}="Service Windows"; $Sheet_tmpl->Cells(1,43)->{Value}="Operation Type"; $Sheet_tmpl->Cells(1,44)->{Value}="Server Commission Date"; $Sheet_tmpl->Cells(1,45)->{Value}="Server Decommission Date"; $Sheet_tmpl->Cells(1,46)->{Value}="Storage Commission Date"; $Sheet_tmpl->Cells(1,47)->{Value}="Storage Decommission Date"; $Sheet_tmpl->Cells(1,48)->{Value}="Application Expritaion Date"; $Sheet_tmpl->Cells(1,49)->{Value}="Recommended Tier"; $Sheet_tmpl->Cells(1,50)->{Value}="Data_Source"; $Sheet_tmpl->Range("A1:AY1")->Font->{FontStyle}="Bold"; $Sheet_tmpl->Range("A1:AY1")->Interior->{ColorIndex}="6"; $Sheet_tmpl->Columns("A:AY")->AutoFit(); my $work_dir = 'c:/Work'; opendir (DIR,$work_dir) || die "\n\nError=>\t$!\n\n"; my @work_files = readdir (DIR); my $trow =2; my $counter=0; foreach my $file (@work_files) { $percent_done = $counter * 100 / $#work_files; $counter++; $mw->update; next if ($file eq '.' || $file eq '..'); my $file_path = $work_dir.'/'.$file; print "\n$file_path\n"; my $xls = $excel->Workbooks->Open("$file_path"); foreach my $sheet ( in $xls->Worksheets ) { my $count =0; my @head_array; my $current_sheet = $sheet->Name, $/; print "$current_sheet\n"; my $new_sheet = $xls->Worksheets("$current_sheet"); my $everything = $new_sheet->UsedRange->{'Value'}; my $array_head = shift @$everything; foreach my $col_heads (@$array_head) { my $rec; if ($col_heads eq "") { $count++; next; } ++$count; #print ++$count. ") $col_heads : \n"; $rec->{$count} = $col_heads; push (@head_array,$rec); } foreach my $ref_array (@$everything) { #print "$ref_array\n"; $count = 0; foreach my $data (@$ref_array) { if ($data eq "") { ++$count; next; } ++$count; #print ++$count.") $data\t"; for my $cell_info (@head_array) { for my $ref_a (keys %$cell_info) { #print "$cell_info->{$count}\n"; eval {$Sheet_tmpl->Cells($trow,1)->{'Value'} = + "$data" if ($cell_info->{$count} eq 'Application'); $Sheet_tmpl->Cells($trow,2)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'Integrity'); $Sheet_tmpl->Cells($trow,3)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'Availability'); $Sheet_tmpl->Cells($trow,4)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'RTO'); $Sheet_tmpl->Cells($trow,5)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'RPO'); $Sheet_tmpl->Cells($trow,6)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'SLA'); $Sheet_tmpl->Cells($trow,7)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'Host Name'); $Sheet_tmpl->Cells($trow,8)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'Location'); $Sheet_tmpl->Cells($trow,9)->{'Value'} = "$dat +a" if ($cell_info->{$count} eq 'SAN'); $Sheet_tmpl->Cells($trow,10)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'NAS'); $Sheet_tmpl->Cells($trow,11)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'DAS'); $Sheet_tmpl->Cells($trow,12)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Current Tier'); $Sheet_tmpl->Cells($trow,13)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Allocated GB'); $Sheet_tmpl->Cells($trow,14)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Actual GB'); $Sheet_tmpl->Cells($trow,15)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Storage Raw (GB)'); $Sheet_tmpl->Cells($trow,16)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Storage Growth (% per year)'); $Sheet_tmpl->Cells($trow,17)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Application Type'); $Sheet_tmpl->Cells($trow,18)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Business Unit'); $Sheet_tmpl->Cells($trow,19)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Business Unit Manager'); $Sheet_tmpl->Cells($trow,20)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Support Person'); $Sheet_tmpl->Cells($trow,21)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Relationship Manager'); $Sheet_tmpl->Cells($trow,22)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Country'); $Sheet_tmpl->Cells($trow,23)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'OLA'); $Sheet_tmpl->Cells($trow,24)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Confidentiality'); $Sheet_tmpl->Cells($trow,25)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Server Role'); $Sheet_tmpl->Cells($trow,26)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Platform'); $Sheet_tmpl->Cells($trow,27)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'HBA Card'); $Sheet_tmpl->Cells($trow,28)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'HBA Card Type'); $Sheet_tmpl->Cells($trow,29)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'HBA Card Total'); $Sheet_tmpl->Cells($trow,30)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'LUNs Available'); $Sheet_tmpl->Cells($trow,31)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Number of Server Processors'); $Sheet_tmpl->Cells($trow,32)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Server Memory'); $Sheet_tmpl->Cells($trow,33)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Server Type'); $Sheet_tmpl->Cells($trow,34)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Frame'); $Sheet_tmpl->Cells($trow,35)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'I/O Throughput'); $Sheet_tmpl->Cells($trow,36)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Data Protection Method'); $Sheet_tmpl->Cells($trow,37)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Number of Replicated Copies'); $Sheet_tmpl->Cells($trow,38)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Location of Replicated Copies'); $Sheet_tmpl->Cells($trow,39)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Backup'); $Sheet_tmpl->Cells($trow,40)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Backup Frequency'); $Sheet_tmpl->Cells($trow,41)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Backup Type'); $Sheet_tmpl->Cells($trow,42)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Service Window'); $Sheet_tmpl->Cells($trow,43)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Operation Type'); $Sheet_tmpl->Cells($trow,44)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Server Commission Date'); $Sheet_tmpl->Cells($trow,45)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Server Decommission Date'); $Sheet_tmpl->Cells($trow,46)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Storage Commission Date'); $Sheet_tmpl->Cells($trow,47)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Storage Decommission Date'); $Sheet_tmpl->Cells($trow,48)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Application Expiration Date'); $Sheet_tmpl->Cells($trow,49)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Recommended Tier'); $Sheet_tmpl->Cells($trow,50)->{'Value'} = "$da +ta" if ($cell_info->{$count} eq 'Data_Source');}; } } } $trow++; } #print "\nLast Row: $LastRow, Last Column: $LastCol\n"; } print "\n____________________________________________\n"; $xls->Close; } Win32::MsgBox("The Operation Completed Successfully\n",0,"Table Insert +ion Manager");

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://466088]
Approved by g0n
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (11)
As of 2015-07-28 23:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (260 votes), past polls