Hello,
I am trying to parse excel sheet in active state perl using Win32::OLE. I am having difficulty even to read the worksheet names if the worksheet position is passed through a hash.
Trying to do: Get position & Worksheet Name (after identifying what each one means <- not important in this issue)

Problem:
I tried 3 methods to loop and get the worksheet name and position but 2 worked and 3rd did not work (Looping though hash). I am more interested in the 3rd one since my code is based on that logic.
Can anyone please help.
Thank you in advance
UVS 
Code:
my $oBook = $Excel->Workbooks->Open("C:\\Inetpub\\wwwroot\\YAI\\WIN_TE
+ST\\INPUT\\abc.xls"); 
$Excel->{DisplayAlerts}=0;


my $sheetcnt = $oBook->Worksheets->Count();
print "---Total wks=$sheetcnt---\n";

# Block 1 works fine
foreach  my $iSheet(1..$sheetcnt){
 my $name=$oBook->Worksheets($iSheet)->{Name};
 my $Sheet = $oBook->Worksheets($iSheet);
print "At Pos=$iSheet Wks name=$name\n";

  if($name=~/Header/){$H_TEST{"HEADER"}{$iSheet}=1;}
  if(($name=~/ALO/)){$H_TEST{"DATA"}{$iSheet}=1;}
}

print "\n-------------------------\n";
# Block 2 works fine
foreach my $Sheet(in $oBook->{Worksheets}){
   print "\t" .$Sheet->{Name} ."\n";
}
print "\n-------------------------\n";

# Prints the content of the hash - works fine
foreach my $tag(sort keys %H_TEST)
{
foreach  my $iSheet(sort keys %{$H_TEST{$tag}}){
    print "Tag=$tag at $iSheet\n";
}
}

print "\n-------------------------\n";

# ---> This block does not work. Particularly
 #  my $sheet = $oBook->Worksheets->{$iSheet};
 # If $iSheet is replaced with numeric value it works

foreach my $tag(sort keys %H_TEST)
{
foreach  my $iSheet(sort keys %{$H_TEST{$tag}}){   
 my $sheet = $oBook->Worksheets->{$iSheet};
 my $name=$oBook->Worksheets($iSheet)->{Name};

print "Tag=$tag Position2=$iSheet and name=$name\n";
}
}

---------------Output with errors--------

Content-type: text/plain

---Total wks=3---
At Pos=1 Wks name=Header
At Pos=2 Wks name=SCM_NAME_RULES
At Pos=3 Wks name=ALO

-------------------------
    Header
    SCM_NAME_RULES
    ALO

-------------------------
Tag=DATA at 3
Tag=HEADER at 1

-------------------------
<h1>Software error:</h1>
<pre>Win32::OLE(0.1707) error 0x80020009: &quot;Exception occurred&quo
+t;
    in METHOD/PROPERTYGET &quot;3&quot; at C:\Inetpub\wwwroot\YAI\TEST
+ING\DB\read_excel_cells.pl line 99
</pre>
<p>
For help, please send mail to this site's webmaster, giving this error
+ message 
and the time and date of the error.

</p>
[Fri Oct 19 11:07:43 2007] read_excel_cells.pl: Win32::OLE(0.1707) err
+or 0x80020009: "Exception occurred"
[Fri Oct 19 11:07:43 2007] read_excel_cells.pl:     in METHOD/PROPERTY
+GET "3" at C:\Inetpub\wwwroot\YAI\TESTING\DB\read_excel_cells.pl line
+ 99

[download]

In reply to Looping Hash keys to get excel wks using Win32::OLE does not work by uvs

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.