From: Todd Burch <promos@...>
Date: 2007-07-17T13:55:10+09:00
Subject: regex help please - parsing an Excel Range

I'm parsing a worksheet in EXCEL, working around (what I consider to be)
unexpected results when merged cells are the last thing on a worksheet.

For conversation sake, lets say my tiny worksheet's whole used range is
A1:D4.

I'm use:

lastcell = sheet.Cells.SpecialCells(xlCellTypeLastCell)

to get the last cell in the sheet, and subsequently I parse out the
maximum row and maximum column used in the worksheet

lastcell.Address         # -> $D$4
maxrow = lastcell.Row    # -> 4
maxcol = lastcell.Column # -> 4  A.K.A. "D"

This works great most of the time.  However, when there is a Merged cell
that goes from A4:D4 in my tiny worksheet, the
SpecialCells(xlCellTypeLastCell) return A4:D4.  This seems ok at first,
until...

lastcell.Address         # -> $A$4:$D$4
maxrow = lastcell.Row    # -> 4  as epxected
maxcol = lastcell.Column # -> 1  A.K.A. "A" - this is not expected!

Therefore, I want to parse out the back half of the range (the $D$4) to
get the actual last row and last column when lastcell.MergeCells is
true.

I've worked this novice regex series out (using a different example),
but I'm thinking it could much more elegant:

addr = "$G$28:$I$28"   # merged cell range
addr =~ /:\$/          # isolates the "I$28" into $'
back = $'              # puts "I$28" into a variable
back =~ /\$/           # "I" and "28" go into $` and $' respectively
puts "row=#{$'}, Col=#{$`}";   # -> Row=28, Col=I - Bingo!

I then simply let EXCEL convert I to 9.  (or AA to 27, etc...)

Any insight for improvement is appreciated - for both the regex and my
usage of EXCEL in this situation.

Thanks, Todd

-- 
Posted via http://www.ruby-forum.com/.