Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Win32::OLE - Find a cell matching a value 1

Status
Not open for further replies.
TommyIndigo

TommyIndigo

Programmer
Joined
Sep 14, 2000
Messages
67
Location
US
I'm using Win32::OLE, and need to extract some data from multiple workbooks/worksheets. My problem is that the target data isn't always in the same cells.

For instance, on one workbook, the value for cell A1 is the string "Last Name". Then, the value in B2 would be the last name the user entered.

However, if the user added rows, lets say 4, the heading would be in A5 and the name entered in B5. There may also have been added columns, further complicating matters.

I'd like to search for the string, "Last Name" and return the value from one cell to the right. Any ideas? This is actually a simplified example, but if I can do the above, I should be able to modify it for my specific needs.

NOTE: I am NOT using the ParseExcel module, as my workbooks involve multiple sheets with heavy macros and protection.

Thanks!
 
Sort by date Sort by votes
Just to reword my question a bit...

I can already work with the Worksheet object, and am able to return the value of a given cell.

I'd like to be able to loop through a range and return the CELL NAME (i.e, "A5") where the cell VALUE matches a particular given value. From there, I should be able to do the rest.

If any gurus out there could PLEASE help me, it would be MOST appreciated! Thanks :)
 
Upvote 0 Downvote
Ok buddy!

Here we go. Actually I replied something similar in the past, but I did not find it..

The trick is iterate over all worksheets. Get current row and column when you find "Last name", then increase column number and get the value.


Code: 
[gray]#!/opt/nokianms/bin/perl[/gray]

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]OLE[/green][red];[/red]
[black][b]use[/b][/black] [green]Win32::OLE[/green][red];[/red]
[black][b]use[/b][/black] [green]Win32::OLE[/green] [red]qw([/red][purple]in with[/purple][red])[/red][red];[/red]
[black][b]use[/b][/black] [green]Win32::OLE::Variant[/green][red];[/red]
[black][b]use[/b][/black] [green]Win32::OLE::Const[/green] [red]'[/red][purple]Microsoft Excel[/purple][red]'[/red][red];[/red]

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$xlsfile[/blue]=[red]"[/red][purple]C:[purple][b]\\[/b][/purple]Documents and Settings[purple][b]\\[/b][/purple]dmazzini[purple][b]\\[/b][/purple]Desktop[purple][b]\\[/b][/purple]TEST.xls[/purple][red]"[/red][red];[/red]
[maroon]open_excel[/maroon][red]([/red][red])[/red][red];[/red]


[gray][i]# ###################################################################[/i][/gray]
[gray][i]#  Sub Open Excel File[/i][/gray]
[gray][i]# ###################################################################[/i][/gray]


[url=http://perldoc.perl.org/functions/sub.html][black][b]sub[/b][/black][/url] [maroon]open_excel[/maroon] [red]{[/red] [gray][i]#Open Sub[/i][/gray]
    
[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple]Processing Excel File:[blue]$xlsfile[/blue][purple][b]\n[/b][/purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]

[blue]$Excel[/blue] = Win32::OLE->[maroon]GetActiveObject[/maroon][red]([/red][red]'[/red][purple]Excel.Application[/purple][red]'[/red][red])[/red] ||
	   Win32::OLE->[maroon]new[/maroon][red]([/red][red]'[/red][purple]Excel.Application[/purple][red]'[/red][red])[/red][red];[/red]
[blue]$Excel[/blue]->[red]{[/red][red]'[/red][purple]Visible[/purple][red]'[/red][red]}[/red] = [fuchsia]0[/fuchsia][red];[/red]		[gray][i]#0 is hidden, 1 is visible[/i][/gray]
[blue]$Excel[/blue]->[red]{[/red]DisplayAlerts[red]}[/red]=[fuchsia]0[/fuchsia][red];[/red]	    [gray][i]#0 is hide alerts[/i][/gray]

[black][b]my[/b][/black] [blue]$Book_Data[/blue] = [blue]$Excel[/blue]->[maroon]Workbooks[/maroon]->[maroon]Open[/maroon][red]([/red][blue]$xlsfile[/blue][red])[/red][red];[/red] [gray][i]# open Excel file[/i][/gray]
[blue]$sheetcnt[/blue] = [blue]$Book_Data[/blue]->[maroon]Worksheets[/maroon]->[maroon]Count[/maroon][red]([/red][red])[/red][red];[/red]
[blue]$iSheet[/blue]=[fuchsia]1[/fuchsia][red];[/red] 

[olive][b]foreach[/b][/olive] [red]([/red][blue]$iSheet[/blue]..[blue]$sheetcnt[/blue][red])[/red][red]{[/red]
   [black][b]print[/b][/black] [red]"[/red][purple]Processing SHEET:[blue]$iSheet[/blue][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
   [blue]$icol[/blue]=[fuchsia]0[/fuchsia][red];[/red][blue]$irow[/blue]=[fuchsia]0[/fuchsia][red];[/red]    
   [blue]$Sheet_compare[/blue] = [blue]$Book_Data[/blue]->[maroon]Worksheets[/maroon][red]([/red][blue]$iSheet[/blue][red])[/red][red];[/red]      

   [gray][i]# Find Last Column and Row[/i][/gray]
   
   [blue]$LastRow[/blue] = [blue]$Sheet_compare[/blue]->[maroon]UsedRange[/maroon]->[maroon]Find[/maroon][red]([/red][red]{[/red][purple]What[/purple]=>[red]"[/red][purple]*[/purple][red]"[/red],
            [purple]SearchDirection[/purple]=>xlPrevious,
            [purple]SearchOrder[/purple]=>xlByRows[red]}[/red][red])[/red]->[red]{[/red]Row[red]}[/red][red];[/red]

   [blue]$LastCol[/blue] = [blue]$Sheet_compare[/blue]->[maroon]UsedRange[/maroon]->[maroon]Find[/maroon][red]([/red][red]{[/red][purple]What[/purple]=>[red]"[/red][purple]*[/purple][red]"[/red],
            [purple]SearchDirection[/purple]=>xlPrevious,
            [purple]SearchOrder[/purple]=>xlByColumns[red]}[/red][red])[/red]->[red]{[/red]Column[red]}[/red][red];[/red]

   [gray][i]# Range for Excel Data[/i][/gray]

   [blue]$firstcell[/blue]=[red]"[/red][purple]A[/purple][red]"[/red][fuchsia].1[/fuchsia][red];[/red]
   [blue]$lastcell[/blue]= [red]"[/red][purple]IV[/purple][red]"[/red].[blue]$LastRow[/blue][red];[/red]   [gray][i]#(Last Column Excel File. Last Row with Data)[/i][/gray]

   [black][b]my[/b][/black] [blue]$array[/blue] = [blue]$Sheet_compare[/blue]->[maroon]Range[/maroon][red]([/red][red]"[/red][purple][blue]$firstcell[/blue]:[blue]$lastcell[/blue][/purple][red]"[/red][red])[/red]->[red]{[/red][red]'[/red][purple]Value[/purple][red]'[/red][red]}[/red][red];[/red]
   [gray][i]#%LOCATION=();[/i][/gray]
   [maroon]do_sheet_compare[/maroon][red]([/red][blue]$array[/blue][red])[/red][red];[/red]
   [blue]$iSheet[/blue]++[red];[/red]
   
      
 [red]}[/red]
 

 
[blue]$Book_Data[/blue]->[maroon]SaveAs[/maroon][red]([/red][blue]$xlsfile[/blue][red])[/red][red];[/red]

[blue]$Excel[/blue]->[red]{[/red][red]'[/red][purple]Visible[/purple][red]'[/red][red]}[/red] = [fuchsia]1[/fuchsia][red];[/red]
[url=http://perldoc.perl.org/functions/undef.html][black][b]undef[/b][/black][/url] [blue]$Book_Data[/blue][red];[/red]
[black][b]undef[/b][/black] [blue]$Excel[/blue][red];[/red]
[black][b]print[/b][/black] [red]"[/red][purple]Checking is done! See you soon[purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]

[gray][i]# Getting all lastnames[/i][/gray]

[olive][b]foreach[/b][/olive] [blue]$i[/blue] [red]([/red][url=http://perldoc.perl.org/functions/sort.html][black][b]sort[/b][/black][/url] [red]{[/red][blue]$a[/blue] <=> [blue]$b[/blue][red]}[/red] [url=http://perldoc.perl.org/functions/keys.html][black][b]keys[/b][/black][/url] [blue]%LOCATION[/blue] [red])[/red] [red]{[/red]
		    [olive][b]foreach[/b][/olive] [blue]$j[/blue] [red]([/red][black][b]sort[/b][/black] [red]{[/red][blue]$a[/blue] <=> [blue]$b[/blue][red]}[/red] [black][b]keys[/b][/black] [blue]%[/blue][red]{[/red][blue]$LOCATION[/blue][red]{[/red][blue]$i[/blue][red]}[/red][red]}[/red][red])[/red] [red]{[/red]	
		        [olive][b]foreach[/b][/olive] [blue]$k[/blue] [red]([/red][black][b]sort[/b][/black] [red]{[/red][blue]$a[/blue] <=> [blue]$b[/blue][red]}[/red] [black][b]keys[/b][/black] [blue]%[/blue][red]{[/red][blue]$LOCATION[/blue][red]{[/red][blue]$i[/blue][red]}[/red][red]{[/red][blue]$j[/blue][red]}[/red][red]}[/red][red])[/red] [red]{[/red]		      
			         [black][b]print[/b][/black] [red]"[/red][purple]ROW=>[blue]$i[/blue],COL=> [blue]$j[/blue],SHEET=>[blue]$k[/blue],LASTNAME:[blue]$LOCATION[/blue]{[blue]$i[/blue]}{[blue]$j[/blue]}{[blue]$k[/blue]} [purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]                    
			[red]}[/red]
		    [red]}[/red]		  
[red]}[/red]


[red]}[/red] 

[gray][i]####################################################################[/i][/gray]
[gray][i]#  Sub Comparison Routine[/i][/gray]
[gray][i]####################################################################[/i][/gray]

[black][b]sub[/b][/black] [maroon]do_sheet_compare[/maroon][red]{[/red]        
        [black][b]my[/b][/black][red]([/red][blue]$array[/blue][red])[/red]= [blue]@_[/blue][red];[/red]
        [blue]$irow[/blue]=[fuchsia]1[/fuchsia][red];[/red]
        [blue]$icol[/blue]=[fuchsia]1[/fuchsia][red];[/red]        
        [olive][b]foreach[/b][/olive] [black][b]my[/b][/black] [blue]$ref_array[/blue] [red]([/red][blue]@$array[/blue][red])[/red] [red]{[/red] [gray][i]# Open for [/i][/gray]
                 [blue]@columns[/blue]=[red]([/red][fuchsia]1[/fuchsia] .. [blue]$LastCol[/blue][red])[/red][red];[/red] 
                 [olive][b]for[/b][/olive] [blue]$col[/blue][red]([/red][blue]@columns[/blue][red])[/red][red]{[/red]
                     [blue]$data[/blue]=[blue]@$ref_array[/blue][red][[/red][blue]$col[/blue]-[fuchsia]1[/fuchsia][red]][/red][red];[/red]
                      [blue]$data[/blue]=[maroon]trim[/maroon][red]([/red][blue]$data[/blue][red])[/red][red];[/red]
                      [olive][b]if[/b][/olive] [red]([/red][blue]$data[/blue] eq [red]"[/red][purple]Last Name[/purple][red]"[/red][red])[/red][red]{[/red]                          
                          [blue]$newcol[/blue]=[blue]$col[/blue]+[fuchsia]1[/fuchsia][red];[/red]
                          [blue]$lastname[/blue] = [blue]$Sheet_compare[/blue]->[maroon]Cells[/maroon][red]([/red][blue]$irow[/blue],[blue]$newcol[/blue][red])[/red]->[red]{[/red][red]'[/red][purple]Value[/purple][red]'[/red][red]}[/red][red];[/red] 
                          [blue]$LOCATION[/blue][red]{[/red][blue]$irow[/blue][red]}[/red][red]{[/red][blue]$newcol[/blue][red]}[/red][red]{[/red][blue]$iSheet[/blue][red]}[/red]=[blue]$lastname[/blue][red];[/red] [gray][i]# in case you want to save all last names[/i][/gray]
                           [black][b]print[/b][/black] [red]"[/red][purple]I have found Last Name in Sheet:[blue]$iSheet[/blue]. (Row,Col)=>([blue]$irow[/blue],[blue]$col[/blue]), it is [blue]$lastname[/blue] ([blue]$irow[/blue],[blue]$newcol[/blue])[purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]                     
                         
                      [red]}[/red]
                                     
                  [red]}[/red]
        [blue]$irow[/blue]++[red];[/red]                                                           
        [red]}[/red] 

[red]}[/red]
   
[gray][i]# ###################################################################[/i][/gray]
[gray][i]# Sub Routine Trim. Remove blanks spaces (right and left)[/i][/gray]
[gray][i]# ###################################################################[/i][/gray]

[black][b]sub[/b][/black] [maroon]trim[/maroon] [red]{[/red]
    [black][b]my[/b][/black] [blue]@out[/blue] = [blue]@_[/blue][red];[/red]
    [olive][b]for[/b][/olive] [red]([/red][blue]@out[/blue][red])[/red] [red]{[/red]
        [red]s/[/red][purple]^[purple][b]\s[/b][/purple]+[/purple][red]/[/red][purple][/purple][red]/[/red][red];[/red]          [gray][i]# trim left[/i][/gray]
        [red]s/[/red][purple][purple][b]\s[/b][/purple]+$[/purple][red]/[/red][purple][/purple][red]/[/red][red];[/red]          [gray][i]# trim right[/i][/gray]
    [red]}[/red]
    [url=http://perldoc.perl.org/functions/return.html][black][b]return[/b][/black][/url] [blue]@out[/blue] == [fuchsia]1[/fuchsia]
              ? [blue]$out[/blue][red][[/red][fuchsia]0[/fuchsia][red]][/red]   [gray][i]# only one to return[/i][/gray]
              : [blue]@out[/blue][red];[/red]     [gray][i]# or many[/i][/gray]
           
[red]}[/red] 
[teal]__END__[/teal]


[teal]Output:[/teal]

[teal]C:\Documents and Settings\dmazzini\Desktop\RIC-SETUP\Examples\OLE-EXCEL>p[/teal]
[teal]Processing Excel File:C:\Documents and Settings\dmazzini\Desktop\TEST.xls[/teal]

[teal]Processing SHEET:1[/teal]
[teal]I have found Last Name in Sheet:1. (Row,Col)=>(3,2), it is Jose (3,3)[/teal]
[teal]I have found Last Name in Sheet:1. (Row,Col)=>(7,7), it is TETE (7,8)[/teal]
[teal]Processing SHEET:2[/teal]
[teal]I have found Last Name in Sheet:2. (Row,Col)=>(4,2), it is Daniel (4,3)[/teal]
[teal]Processing SHEET:3[/teal]
[teal]I have found Last Name in Sheet:3. (Row,Col)=>(7,2), it is Pedro (7,3)[/teal]
[teal]Checking is done! See you soon[/teal]
[tt]------------------------------------------------------------
Other Modules used :
[ul]
[li]OLE[/li]
[li]Win32::OLE[/li]
[li]Win32::OLE[/li]
[li]Win32::OLE::Const[/li]
[li]Win32::OLE::Variant[/li]
[/ul]
[/tt]






dmazzini
GSM System and Telecomm Consultant

 
Upvote 0 Downvote
Thank you SO much for the comprehensive answer...this is EXACTLY what I needed!!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ss_ham
  • Locked
  • Question Question
in Perl find call looking to exclude folder and ignore duplicate finds.
Replies
2
Views
720
ss_ham
ss_ham
Dino_1000
  • Locked
  • Question Question
Use the perl debugger to dump all the data in a string variable
Replies
0
Views
665
Dino_1000
Dino_1000
dan
  • Locked
  • Question Question
add a variable to subject in formmail.pl
Replies
6
Views
829
feherke
feherke
Limbomusic
  • Locked
  • Question Question
Perl files getting accessed directly
Replies
0
Views
644
Limbomusic
Limbomusic
newbie1238585
  • Locked
  • Question Question
Perl how to read string in csv file * as wildcard operator
Replies
2
Views
821
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top