Win32::OLE Excel Extracting Comments

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I need to extract comments from lots of Excel spreadsheets. So I've started learning Win32::OLE. I found the a great reference here on perlmonks: Win32::OLE Excel Comment Box.

I'm sure the following line is wrong, but i don't know how to change it.

      my $commenttxt = $comment1->Text();
[download]

I get the message: cannot call method "Text". Where can I get a list of methods?

#!e:/perl/bin/perl.exe

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

my($value,$formula);

$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
my $Book = $Excel->Workbooks->Open("C:/temp/test/test1.xls");

# select worksheet number 1 (you can also select a worksheet by name)
my $Sheet = $Book->Worksheets(1);

#test case limit to first few cols and rows
foreach my $row (1..4)
{
 foreach my $col (1..3)
 {
  # skip empty cells
  next unless defined $Sheet->Cells($row,$col)->{'Value'};

      my $cell1 = $Sheet->Cells($row,$col);  
      my $comment1 = $cell1->Comment();

      my $commenttxt = $comment1->Text();

      
     # print out the contents of a cell  
 $value=$Sheet->Cells($row,$col)->{'Value'};
 $formula=$Sheet->Cells($row,$col)->{'Formula'};

 #print the comments
 printf("Comment: $commenttxt\n");
 
 if($value eq $formula) {
      printf("Value:$value\n");
 } else {
      printf("Formula:$formula, value:$value\n");
 }      
}
}
      
# clean up after ourselves
$Book->Close;
[download]

edited: Thu Oct 23 19:17:24 2003 by jeffa - code tags (1st line only), switch <a> tag to in-house id tag

Comment on Win32::OLE Excel Extracting Comments Select or Download Code

Replies are listed 'Best First'.
Re: Win32::OLE Excel Extracting Comments by Nkuvu (Priest) on Oct 23, 2003 at 18:42 UTC
You can see Excel stuff by going into Excel, and typing `alt-F11`. This brings you to the VB editor, where you can press `F2` to see the browser. I normally set the drop-down box to Excel (from the default value of "<All Libraries>") so I don't end up looking at Word functions. As far as the error goes, Comment doesn't appear to be a function of a Range object (which is what `$Sheet->Cells` returns), Comment is a property of the range. So I'd try something like `my $commentTxt = $Sheet->Cells($row, $col)->Comment->Text();` -- note that you don't have to store everything into an intermediate variable, you can just string it all together if it makes sense. Also note that this is untested -- and you should think about adding checks to each of your function calls to ensure that they completed successfully. Update: I should take my own advice. Test the function call return.	[reply] [d/l] [select]
Re: Win32::OLE Excel Extracting Comments by Nkuvu (Priest) on Oct 23, 2003 at 22:39 UTC
Urg. Does your top left cell have a comment in it? If not, you need to make sure you check before trying to run a command on the non-existent Comment object. Consider the following code snippet: `$comment = $Sheet->Cells($row,$col)->Comment; if ($comment) { $commenttxt = $comment->Text(); }` [download] The Text function call fails if there isn't a Comment object stored in your cell. And crashes the script. Duh. I should have thought of this a lot earlier. Or tested.	[reply] [d/l]
Re: Win32::OLE Excel Extracting Comments by Grygonos (Chaplain) on Oct 23, 2003 at 20:25 UTC
Comment is it's own object with a Text Method, but referencing the Text method in those docs, doesn't list it as applying to the comment object... weird huh? My guess is also `$worksheet->{$range_one,$range_one}->Comment->Text()` The reason I think yours may not have worked is because its a function of a Range of cells and not one cell if you try it with your range as 1 cell like i mentioned above... using say A1,A1 as your range, then maybe it would work.	[reply] [d/l]
Re: Re: Win32::OLE Excel Extracting Comments by Nkuvu (Priest) on Oct 23, 2003 at 20:45 UTC
According to the VB browser in Excel, the Cells function returns a "cell as Range". So it's returning a valid Range object, I think. I've used the Cells function every once in a while, although more often I use the Range function. But even specifying a single cell it works just fine. For example: I've frequently used the following to get a value: `$value = $worksheet->Range("B1")->{Value};` and I get the same results from the code `my $value = $worksheet->Cells(1,2)->{Value};`	[reply] [d/l] [select]