[PerkD]

Hello,

I've created some code to extract data from Excel that maintains the column headers and row count. I'm looking for suggestions on how to change the $Header call from Range to selecting values until null. I would also like to know of any suggestions for getting rid of "@Col", so that it is more dynamic.

Thanks

Code:

#!/usr/bin/perl
use OLE;
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;                                # die on errors...
# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  
# open Excel file
print("Opening Excel file\n");
my $Book = $Excel->Workbooks->Open("c:\\Master_DataFile.xls"); 
# select worksheet
print("Getting worksheet\n");
my $Sheet = $Book->Worksheets(1);
my @DataHeaders;    # array of data headers  # should look like ("Row_Num", "TC_Name", "NavPath" ... )
my $Headers;
my @Headers1;
my @DataTable;      # hash table for data
my @Col = ( "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S","T",
"U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ");
my $NumHeaders;
print("Reading headers ...\n");
$Headers = $Sheet->Range("A2:AJ2")->{'Value'};
my $idx=0;
# Put the headers into a local array and print them out
for (@$Headers) {
    for (@$_) {
        @DataHeaders[$idx] = (defined($_) ? "$_" : "<undef>|");
        $idx++;
    }
}
# Determine the number of headers (actually this gives index of the last header read in)
$NumHeaders = $#DataHeaders;
print("NumHeaders = $NumHeaders\n");
# read in the first 2 rows of data and put them into the hash table using $Header as the key
my $j;
my $index;
my $Header;
my $k=0;
my $Cell;
my @Row;
my $Tidx=0;
my $RowName;
my %DT;
for ($j=3; $j <=400; $j++) {
    $k=0;                                               # need to know how many rows are in Excel sheet ???
    foreach $Header (@DataHeaders) {                                     # this traverses the columns
        $index = "$Col[$k]$j";  # for example "A2"                           # this gives us the cell number
            $Cell = $Sheet->Range($index)->{'Value'};
            if ("TC_Name" eq /$Header/i) { 
                $RowName = $Cell; 
            }
            $DataTable[$Tidx]{$Header} = $Cell;
            $DT{$RowName}{$Header} = $Cell;
        $k++;
    }
    $Tidx++;
}
our $variable="Build";
our $row="397";
# To access a specific value, say the 3rd row of TC_Name
if ($DataTable[$row]{$variable}eq"hooty"){
	print"Pass\n";
	print("$row row of $variable = ".$DataTable[$row]{$variable}."\n");
}
else {
	print"Fail\n";
	print($DataTable[$row]{$variable}."\n");
}

print("\nClosing book\n");
$Book->Close;