Excel Range usage with Cells [solved]

[UEZ]

I'm stuck on how to use for range selection .Cells function.

Instead of

_Excel_RangeSort($oWorkbook, Default, Default, "AD:AD", Default, Default, $xlYes, False, Default, "AE:AE", Default, "L:L", Default)

and

$aResult = _Excel_RangeRead($oWorkbook, 1, "S2:AB" & $iRows)

which works properly I want to use Cells to select the range.

 

Why? Because the Excel sheet was modified and an additional row was inserted. I want to create the script more dynamically by selection it with Cells because I can search for the column headers.

Any idea?

 

Thanks.

Edited November 9, 2015 by UEZ

[water]

$oWorkbook.ActiveSheet.Cells(x,y)
In the wiki you'll find a page where some special ranges are described.

[UEZ]

Thanks water!

$aResult = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, 18), $oWorkbook.ActiveSheet.Cells($iRows, 27)))

works for this particular line.

Does it works the same way also for _Excel_RangeSort?

 

Btw, is should be $oWorkbook.ActiveSheet.Cells(y,x)!

 

Edited November 9, 2015 by UEZ

[water]

All _Excel* functions accept a range object or an "A1" notation.
The following properties and methods return a range:

• Range property

• Cells property

• Range and Cells

• Offset property

• Union method

 

Edited November 9, 2015 by water

[UEZ]

Thanks water, I got it worked now.

;before
_Excel_RangeSort($oWorkbook, Default, Default, "AD:AD", Default, Default, $xlYes, False, Default, "AE:AE", Default, "L:L", Default)

;after
_Excel_RangeSort($oWorkbook, Default, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Shelf + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Shelf + 1)), Default, Default, $xlYes, False, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Slot + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Slot + 1)), Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_ModelType + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_ModelType + 1)), Default)



;before
$aResult = _Excel_RangeRead($oWorkbook, 1, "S2:AB" & $iRows)

;after
$aResult = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Range( _
                            $oWorkbook.ActiveSheet.Cells(2, $iPos_Region + 1), _
                            $oWorkbook.ActiveSheet.Cells($iRows, $iPos_GN + 1)))

 

Outlook issue still ongoing...

 

Edit:

_Excel_RangeSort($oWorkbook, Default, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Shelf + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Shelf + 1)), Default, Default, $xlYes, False, Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_Slot + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_Slot + 1)), Default, _
    $oWorkbook.ActiveSheet.Range($oWorkbook.ActiveSheet.Cells(2, $iPos_ModelType + 1), $oWorkbook.ActiveSheet.Cells($iRows, $iPos_ModelType + 1)), Default)

doesn't work properly. The transformation e.g. of "AD:AD" seems to be different.

Workaround:

$oMatch = $oWorkbook.ActiveSheet.Range("A1:ZA1").Find("Shelf", Default, $xlValues, $xlPart, Default, Default, False)
$sPos_Shelf = StringRegExpReplace($oMatch.Address, "\$(.+)\$\d*", "$1")
$oMatch = Null
$oMatch = $oWorkbook.ActiveSheet.Range("A1:ZA1").Find("Slot", Default, $xlValues, $xlPart, Default, Default, False)
$sPos_Slot = StringRegExpReplace($oMatch.Address, "\$(.+)\$\d*", "$1")
$oMatch = Null
$oMatch = $oWorkbook.ActiveSheet.Range("A1:ZA1").Find("Model Type", Default, $xlValues, $xlPart, Default, Default, False)
$sPos_ModelType = StringRegExpReplace($oMatch.Address, "\$(.+)\$\d*", "$1")
$oMatch = Null

 

Edited November 10, 2015 by UEZ

[water]

Glad you got it working

The Outlook issue is still on my ToDo-List but I'm quite busy at the moment. Can only spend a few minutes to answer some threads but do not have the time to check your problem at the moment.
Will keep you informed.