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

Hello all, i am trying use 'Find' method of excel butr i am getting error:OLE exception from "Microsoft Excel": Unable to get the Find property of the WorksheetFunction class Win32::OLE(0.1709) error 0x800a03ec in METHOD/PROPERTYGET "Find" at D:\Garg_scripts\KV\demoscript.pl line 44 8
 use Cwd 'abs_path';
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Excel";
$Win32::OLE::Warn = 3;

my $start_run = time();

print"[USAGE]: <demoscript.pl> <.map file path> <Ouput excel path>\n";

print"[INFO]: Executing demoscript.pl V1.0.1.\n";


my $CLEA_Family = $ARGV[0];    
    


my $Excel =Win32::OLE->new('Excel.Application');

# Open Excel file
my $Book = $Excel->Workbooks->Open($CLEA_Family);

#Make Excel visible
$Excel->{Visible} = 1;

my $Sheet = $Book->Worksheets("IB_MsgSig");

my $worksheetfunction = $Excel->WorksheetFunction();



my $value = $worksheetfunction->Find("IB_MsgSig","E","Go Notifier Cust
+omization Availability Flag 2");

print"\n $value";

$Book->Save; #Or $Book->SaveAs("C:\\file_name.xls");
$Book->Close; #or $Excel->Quit;
print"\n[INFO]Timetaken: $run_time [Secs]";

[download]

Replies are listed 'Best First'.
Re: Error in excel reader
by haukex (Archbishop) on Jan 02, 2019 at 14:43 UTC
    $Excel->WorksheetFunction->Find("IB_MsgSig","E","Go Notifier Customization Availability Flag 2");

    You are accessing WorksheetFunction.Find, which does the same as the Excel function FIND. I suspect you might want Range.Find instead.

[reply]
[d/l]
[reply]
        I'm not so sure, I am definitively finding a method with that prototype

        See e.g. What does Application.WorksheetFunction.Find do?, in other words, it's basically the same as index. Although it is just a guess, based on the arguments the OP is passing to the function, I really do suspect the OP mistook WorksheetFunction.Find for Range.Find, and wants the latter instead.

[reply]
[d/l]
[select]
Re: Error in excel reader
by davies (Monsignor) on Jan 02, 2019 at 14:44 UTC 
    use strict;
use warnings;
use Win32::OLE;
use feature 'say';

my $Excel = Win32::OLE->new('Excel.Application');
$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Add;
for my $nSht (2..$Book->Sheets->{Count}) {
    $Book->Sheets(2)->Delete;
}

my $Sheet = $Book->Sheets(1);
say $Sheet->UsedRange->Find('Go');
$Sheet->Cells(3,4)->{Value} = 'Go';
say $Sheet->UsedRange->Find('Go')->Address;
$Excel->{DisplayAlerts} = 0;
$Excel->Quit;

    [download]

    You may be over-complicating things. The only real complication in the SSCCE above is that Find returns undef if it fails. That is the difference in my two say statements - an undefined object cannot have an address. You may need to test for this, but I don't have your files and so can't know.

    Regards,

    John Davies

[reply]
[d/l]
[select]
Re: Error in excel reader
by Veltro (Hermit) on Jan 02, 2019 at 14:46 UTC

    Another sugestion here that you may be able to try. The third argument of the Find method does not seem to be a string to me but an object. Looking at the Win32::OLE::Variant Perl documentation, I don't have experience with this but I think you can try to:

    my $tmpstr = Variant( VT_BSTR, "Go Notifier Customization Availability
+ Flag 2" ) ;
my $value = $worksheetfunction->Find("IB_MsgSig","E",$tmpstr) ;

    [download]

    edit: Another thing that you could try is to specify the range as E:E

[reply]
[d/l]
[select]

      I don't see how this would help. VBA tries to be strongly typed but then finds it needs to have weak typing available. The variant is its weakly typed scalar, so, unless I have something wrong, your suggestion would involve coercing a string into a variant in a situation where it is known to be a string & can be strongly typed. While putting it in a variable shortens the code, which can be desirable, the advantage of a variant isn't immediately obvious to me.

      Regards,

      John Davies

[reply]
Re: Error in excel reader
by thanos1983 (Parson) on Jan 02, 2019 at 14:25 UTC

    Hello sainky

    I do not have a WindowsOS so I can not replicate your error. Although based on a previous similar thread Problem opening WriteExcel generated spreadsheets with OLE, it looks the error is coming from opening the file due to the path.

    Can you add this part on your code and let us know if it works?

    #!/usr/bin/perl
use Cwd;
use strict;
use warnings;
use Cwd 'abs_path';

my $file = $ARGV[0];
my $dir = getcwd;
my $abs_path = abs_path($file);
print $abs_path . "\n";

    [download]

    Update: My assumption regarding the path of the file was wrong. I did not read clearly your question so my answer is not valid. Fellow Monks have provided you with proposed solutions.

    Looking forward to your update. Hope this helps.

    BR / Thanos

    Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom