STDIN Format

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

So I am trying to find a cell in a spreadsheet. I entered "findme" in the cell and I want to return the cell next to it. So if "findme" was in A1, I want to return B1. If I hard code the script to find "findme" it works just fine. I want to prompt the user so it can be more versatile, but it will not work correctly. Any Ideas?

#use warnings;  
#use strict;  
use Cwd;  
use OLE;  
use Win32::OLE::Const "Microsoft Excel";  
 
#-----------------------"Copy From" Destination-----------------------
+--
  my ( $xl_report ) = ( "c:/perl/energy.xls" );  
  my ( $excel, $workbook, $sheet, $row, @data,$i, $j,$char,$char2,$pri
+ntme,$FindMe, @FindMe ); 
   $excel = CreateObject OLE 'Excel.Application';  
   $excel -> {Visible} = 1;  
   $excel -> {EnableEvents} = "false"; 
 
     #-----------------------Formating and Setup----------------------
+-----
   $workbook = $excel    -> Workbooks  -> Open ( "$xl_report" ) or &te
+rminate ( "open $xl_report" );  
   $sheet    = $workbook -> Worksheets( "info" );
   $sheet -> Activate;  
    #---------------------Search for Text-----------------------------
+---
    #print "Find what !?\n"; 
        #$FindMe = <STDIN>;#I also tried multiple types
for ($j=1; $j<27; $j++)#loop to go through Columns
{
    $FindMe= findme;
    $char= chr($j+64);
    $char2= chr($j+65);
    for ($i=1; $i<30; $i++)#loop to go through Rows
    {
         if (($sheet -> Range ( $char . $i ) -> {Value}) eq $FindMe)
         {
             $data = $sheet -> Range ( $char2 . "$i" ) -> {Value};
         }
    }
}
    $sheet -> Range ( "Z" . 1 ) -> {Value} = $data;
    print "ENDDATA: $data";
[download]

Comment on STDIN Format Download Code

Replies are listed 'Best First'.
Re: STDIN Format by walto (Pilgrim) on Mar 31, 2008 at 18:47 UTC
Change `$FindMe = <STDIN>;#I also tried multiple types for ($j=1; $j<27; $j++)#loop to go through Columns` [download] to `$FindMe = <STDIN>; chomp ($FindMe); for ($j=1; $j<27; $j++)#loop to go through Columns` [download] $FindMe contains the newline character. chomp eliminates the trailing newline.	[reply] [d/l] [select]
Re: STDIN Format by NetWallah (Canon) on Mar 31, 2008 at 19:24 UTC
The chomp suggestions above should resolve your immediate issues. I offer the following style suggestions, as an alternative I consider superior: 1. For more idomatic perl, consider using the following alternative way to write your "for $j" and "for $i" loops. `for my $letter("A".."B"){ for my $num(1..5){ print qq[$letter$num ]; } } # Prints the sequence -- # A1 A2 A3 A4 A5 B1 B2 B3 B4 B5` [download] 2. Instead of writing code to do your search, you could make Excel do the work for you, by using the Find method of the Range object. Details at MSDN. (FYI: I had previously posted this in response to Simple Question - Simple Answer, I hope) "As you get older three things happen. The first is your memory goes, and I can't remember the other two... " - Sir Norman Wisdom	[reply] [d/l]
Re: STDIN Format by tcf03 (Deacon) on Mar 31, 2008 at 18:48 UTC
try this in your code. `my $FindMe = <STDIN>; chomp ($FindMe);` [download] Ted -- "That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved." --Ralph Waldo Emerson	[reply] [d/l]
Re: STDIN Format by Anonymous Monk on Apr 01, 2008 at 12:54 UTC
Hey Thanks for all the help, it was the chomp() that did it. I will look into the FIND and Range functions, thanks for the suggestions.	[reply]