comment on

Hello Monks, I humbly seek any and all guidance you can give me. I have three issues I am trying to solve:

1. How do you match an empty excel cell?
I use the $Sheet->UsedRange()->{'Value'} expression to retrieve all used objects. But the range is off by a few hundred rows.

I'm trying to match the cell like this:

if ($rangeobject =~ /""/)
(I'm told that in VB you match with this expression: <"">

2. I want to loop through the range object row by row. And return all the defined cells in that row. How can I do this with OLE? I have code that will let me iterate over the cells but not over the range.

3. The excel API is very picky about the file it wants to open. If I hardcode the value I can open the file in excel. If I try to pick a value from a traversed directory it fails. See below:


#!c:\perl\bin\perl -w

use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

if(scalar @ARGV != 2) {

    usage();
    exit 1;
}

my $outputFile = $ARGV[0];
my $inputDir   = $ARGV[1];

open (OUTFILE, ">$outputFile");

&traverse($inputDir);

exit 1;

##########################
# traverse directories
#########################
sub traverse {
    my($dir) = shift;
    my($path);
    unless (opendir(DIR, $dir)) {
        warn "Can't open $dir\n";
        closedir(DIR);
        return;
    }
    foreach (readdir(DIR)) {
        next if $_ eq '.' || $_ eq '..';
        $path = "$dir/$_";
        if (-d $path) {     # a directory
            &traverse($path);
        } 
        elsif (-f _) {  # a plain file
        
                next if ( $_ !~ /\.xls$/i);
        &open_xls($path);
        } #else-if file
    }
    closedir(DIR);
}

##########################
# Open spreadsheet
#########################
sub open_xls{
  my $path = shift;
  print "PATH1=$path\n";

  $path =~ s/\//\\\\/;
  print "PATH2=$path\n";

  $Win32::OLE::Warn = 3;                                # die on error
+s...

# get already active Excel application or open new
  my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit')  or die Win32::OLE
+->LastError(); 

# open Excel file
  my $Book = $Excel->Workbooks->Open($path) or die Win32::OLE->LastErr
+or(); 

  my $wkSheetCount = $Book->Worksheets->Count;

  foreach my $sheetnum (1..$wkSheetCount)
  {
     my $Sheet = $Book->Worksheets($sheetnum);
     $Sheet -> Activate();
     
     my $name = $Sheet->Name;
#       print "Working on sheet # $sheetnum - Its name is $name\n";

     my $everythingArray=$Sheet->UsedRange()->{'Value'};
#    my $everythingArray = $Sheet->Range("A8:B9")->{'Value'};
    
    foreach my $ref_array (@$everythingArray) {    
    foreach my $scalar (@$ref_array) {
         print "$scalar\t";
     }
     print "\n";
    }
  } #foreach sheet



$Book->Close;
undef $Book;
$Excel->Quit;
}


sub usage{
    print "Usage:\n" .
        "\tperl $0 OutputFile DirectoryPath\n";
}
[download]

Edit, BazB: add readmore

In reply to WIN::32 OLE Excel help needed by Anonymous Monk

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.