Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re^6: Can't Find my way in Excel

by ExReg (Priest)
on Sep 17, 2019 at 19:00 UTC ( #11106316=note: print w/replies, xml ) Need Help??


in reply to Re^5: Can't Find my way in Excel
in thread Can't Find my way in Excel

My apologies for bad transcription. It is hard moving from computer to computer, copying and typing. I was hoping it was obvious what I was trying to do. The old way was what is shown above. The new way I am trying to find is replacing the search by row and cell and just use a Find. So the new code would be

use strict; use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; my $Excel = Win32::OLE->new('Excel.Application','Quit') or die "Cannot + open Excel\n"; my $Book = $Excel->workbooks->Open(FileName => "c:/foo.xlsx", ReadOnly + => 1) or die "Cannot open Spreadsheet\n"; my $num_sheets = $Book->Sheets->Count; for my $sheet_num ( 1 .. $num_sheets ) { my $Sheet = $Book->worksheets( $sheet_num ); my $max_col = $Sheet->UsedRange->SpecialCells( xlCellTypeLastCell +)->Column; my $max_row = $Sheet->UsedRange->SpecialCells( xlCellTypeLastCell +)->Row; my $loc = $Sheet->UsedRange->Cells->Find({What=>"cabbage"}); }

Running in debug gives "Undefined subroutine &main:: called at (eval 14)D:/software/Perl/lib/perl5db.pl:646 line 2, <$lh> line 7."

Running without debug gives "Can't use string ("") as a subroutine while "strict refs" in use at search.pl line 12, <$lh> line 7."

Again, I am trying to do my best to transcribe.

Replies are listed 'Best First'.
Re^7: Can't Find my way in Excel
by poj (Abbot) on Sep 17, 2019 at 20:14 UTC

    Try

    #!perl use strict; use Win32::OLE 'in'; use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; my $Excel = Win32::OLE->new('Excel.Application','Quit') or die "Cannot open Excel"; my $Book = $Excel->workbooks->Open( 'c:/foo.xlsx') or die "Cannot open Spreadsheet"; for my $Sheet ( in $Book->Worksheets ) { my $first = $Sheet->Cells->Find({What=>"cabbage"}); if ($first->{value}){ printf "%s %-20s %s\n",$Sheet->{name},$first->{address},$first->{v +alue}; my $last = $first; while (my $next = $Sheet->Cells->FindNext($last)){ last if $next->{address} eq $first->{address}; printf "%s %-20s %s\n",$Sheet->{name},$next->{address},$next->{v +alue}; $last = $next; }; } }
    poj

      I bow at your feet, O most wise monk. It is a thing of beauty.

Re^7: Can't Find my way in Excel
by rjt (Deacon) on Sep 17, 2019 at 19:28 UTC

    The line numbers seem to be a bit off, but it looks like $Book->worksheets is probably returning undef, which is leading to all of the errors when you try to call $Sheet->().

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11106316]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (10)
As of 2019-10-23 15:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?