Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re: PROPERTYPUT error in Win32::OLE

by guha (Priest)
on Jun 13, 2005 at 11:00 UTC ( #466103=note: print w/ replies, xml ) Need Help??


in reply to PROPERTYPUT error in Win32::OLE

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


Comment on Re: PROPERTYPUT error in Win32::OLE
Re^2: PROPERTYPUT error in Win32::OLE
by Anonymous Monk on Jun 13, 2005 at 11:56 UTC
    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: note [id://466103]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (8)
As of 2015-07-05 21:07 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 (68 votes), past polls